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 |
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 |
All times are GMT +1. The time now is 10:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com