Copy dynamic range from multiple sheets to summary sheet

Hi,
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.

Re: Copy dynamic range from multiple sheets to summary sheet

Hi junho,

Thanks..that was the detail I missed! It now loops through the sheets.
However, I can see that the 'for each' loops from left to right, whereas I need it to start with the last sheet and move to left.
Having done some research I can see that a 'for each' loop cannot loop in reverse, so I'm working on getting it to loop through the sheets with a 'for next' loop.
Might have to start another thread for help on that one, but will see what I can find first.
Anyways, thanks for your help - it's a great way for me to learn more.
Best,