MOVING A VARIABLE RANGE
 

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

MOVING A VARIABLE RANGE
 

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

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

--
Regards,
Tom Ogilvy


"sylink" wrote in message
oups.com...
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

MOVING A VARIABLE RANGE
 

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

--
Kevin Backmann


"sylink" wrote:

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

MOVING A VARIABLE RANGE
 

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