Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a range using syntax 2
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 -- botha82 ----------------------------------------------------------------------- botha822's Profile: http://www.excelforum.com/member.php...fo&userid=3675 View this thread: http://www.excelforum.com/showthread.php?threadid=56916 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a range using syntax 2
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
syntax - range name as a variable | Excel Programming | |||
Syntax help with range | Excel Programming | |||
syntax to go to a named range | Excel Programming | |||
Range syntax | Excel Programming | |||
Syntax for Range Reference | Excel Programming |