Try this
http://www.rondebruin.nl/copy2.htm

See the currentregion example


--
Regards Ron de Bruin
http://www.rondebruin.nl



"botha822" wrote in message
...

I want to copy a range of multiple sheets and paste it to a summary
sheet. The kicker is that the range changes in length every day and
sometimes will be a different column if the spreadsheet has been
altered. Here is the code I have currently developed, but there seems
to be a problem with my Range syntax. Thanks for any help.

Sub summary()

Dim lastcell As Integer
Dim WsName As String
Dim i As Integer, n As Integer

For i = 9 To 9
If Not IsEmpty(Worksheets("Summary").Cells(2, i).Value) = True
Then
WsName = Worksheets("Summary").Cells(2, i).Value
Worksheets("Summary").Range(Cells(4, i), Cells(2415,
i)).ClearContents
For n = 20 To 21
If Worksheets(WsName).Cells(2, n).Value = "System Cum."
Then
'lastcell = Worksheets(WsName).Range.Cells(4,
n).End(xlDown).Row

Worksheets(WsName).Range(Cells(5, n), Cells(2415,
n)).Copy _
Destination:=Worksheets("Summary").Cells(4, i)
Exit For
End If
Next n
End If
Next i

End Sub


--
botha822
------------------------------------------------------------------------
botha822's Profile: http://www.excelforum.com/member.php...o&userid=36752
View this thread: http://www.excelforum.com/showthread...hreadid=569164