loop action through all sheets in workbook skipping certain sheets

[sawa85sa]

Hi Everyone,

I have created a clunky macro to copy certain data from several sheets one by one and copy that data to my summary sheet, at the moment it works only for that one spread sheet and for only these named sheets that are in the code

Sheets("Sheet 1").Select
Range("A5:I5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("summary sheet").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

This is basically copied within a macro so many times how many sheets I have in the workbook from "Sheet 1" to "Sheet 20" always to dump that data at the end of previously copied data in the "summary sheet"
How do I loop this action so it works on a generic work sheet where there is only certain that there is a "summary sheet" and a random number of Sheets 1 to Sheet 'n' of data that needs to be copied in to that "summary sheet"?

Regards,

Sawa

[stnkynts]

I am not 100% sure on what you want. You explanation was confusing to me. Try this and see how close I got:

Sub MainMacro()
Dim ws As Worksheet:    Set ws = Sheets("Summary")
Dim wksht As Worksheet
Dim LR As Long

For Each wksht In Worksheets
    If Not wksht.Name = "Summary" Then
        LR = wksht.Range("A5").End(xlDown).Row
        wksht.Range("A5:I" & LR).Copy Destination:=ws.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next wksht

End Sub

[sawa85sa]

That's exactly what I've been looking for, thank you. I'm guessing it's one of the basic codes, but I'm just starting with vba and the basic language is confusing for me.

Thanks again.