As you can probably see from my code I'm a newbie who've been searching forums for help but now I'm stuck, so I hope you can point me in the right direction from here, wanting to learn!
What I have:
A workbook with daily draft sheets (named with today's date) and a summary sheet (and a hidden template sheet and a visible frontpage sheet).
What I want (and can't get to work):
Copy a range from the daily draft sheets to the Summary sheet.
The range I want to copy is from C12 to the last used cell in column C and incl. columns C to G.
This range (SourceRng) must be copied to the Summary sheet, in the cell one row below the last used cell in column C.
Starting with the last sheet (lowest date) this range must be copied and then the next sheet etc., e.g. SourceRng on sheet "06-12-10" copied to Summary, then SourceRng on sheet "07-12-10" copied to Summary sheet etc.
Option Explicit Sub summary() Dim sht As Worksheet Dim TargetCell As Range 'cell just below last used cell in column C on sheet "SUMMARY" Dim SourceRng As Range 'range starting in C12 to last used cell in column C and including columns C to G With Application .ScreenUpdating = False For Each sht In ActiveWorkbook.Worksheets Select Case sht.Name Case "FRONTPAGE", "SUMMARY", "Template" 'Skip these sheets by doing nothing Case Else 'can't get this part to work.. Set SourceRng = Range("c12", Range("C65536").End(xlUp).Offset(0, 4)) Set TargetCell = ThisWorkbook.Sheets("SUMMARY").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) SourceRng.Copy TargetCell End Select Next sht .CutCopyMode = False 'clear clipboard .ScreenUpdating = True End With End Sub
Couldn't attach the file because it is 1,05 MB.
Any help is deeply appreciated.