MOVING A VARIABLE RANGE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sylink - 24 Jan 2006 16:02 GMT

I have this problem trying to move a range to another worksheet
For intance I need to highlight and move (also delete leftover blank
spaces) the range containing event column "rev" to sheet 2. The
solution should be able to take any range size (i.e x rows of event
"rev")

SHEET1

NAME    EVENT    DATE
EUROCOMM    VAMB    14/07/2005
EUROCOMM    VAMB    14/07/2005
EUROCOMM    VAMB    14/07/2005
EUROCOMM    rev    18/10/2005
EUROCOMM    rev    25/08/2005
EUROCOMM    rev    01/08/2005
EUROCOMM    rev    28/07/2005
EUROCOMM    init    23/12/2005
EUROCOMM    init    15/12/2005
EUROCOMM    init    14/12/2005
EUROCOMM    init    13/12/2005
EUROCOMM    init    12/12/2005

Reply to this Message

Tom Ogilvy - 24 Jan 2006 16:09 GMT

See if one of the canned solutions at Ron de Bruin's site won't work here:

http://www.rondebruin.nl/copy5.htm

Signature

Regards,
Tom Ogilvy

> I have this problem trying to move a range to another worksheet
> For intance I need to highlight and move (also delete leftover blank
[quoted text clipped - 17 lines]
> EUROCOMM init 13/12/2005
> EUROCOMM init 12/12/2005

Reply to this Message

Kevin B - 24 Jan 2006 16:17 GMT

Assuming that there are no columns separating the data, and its starts at A1
of Sheet 1, the following code will work:

Sub MoveData()

Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set wb = ActiveWorkbook
Set ws1 = wb.Worksheets("Sheet1")
Set ws2 = wb.Worksheets("Sheet2")

ws1.Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Cut
ws2.Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Set wb = Nothing
Set ws1 = Nothing
Set ws2 = Nothing

End Sub

Signature

Kevin Backmann

Reply to this Message

sylink - 26 Jan 2006 08:54 GMT

Thanks Kevin. The solution is expected to move only the part shown
below because the evevnt is "rev".Others to remain in sheet1:

NAME EVENT DATE
EUROCOMM rev 18/10/2005
EUROCOMM rev 25/08/2005
EUROCOMM rev 01/08/2005
EUROCOMM rev 28/07/2005

Reply to this Message