copying a range using syntax 2

[botha822]

I want to copy a range of multiple sheets and paste it to a summar
sheet. The kicker is that the range changes in length every day an
sometimes will be a different column if the spreadsheet has bee
altered. Here is the code I have currently developed, but there seem
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) = Tru
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 Su

 

[Ron de Bruin]

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

See the currentregion example