MOVING A VARIABLE RANGE

[sylink]

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

 

[Tom Ogilvy]

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

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

 

[Kevin B]

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

 

[sylink]

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