CODEWITHSUNDEEP
vbaloopsexcelworksheet
Kaylin Zeuske
Kaylin Zeuske

Reputation: 3

Loop to go through each worksheet in workbook

I'm trying to loop through each worksheet in the workbook but it's not working. I think it's the ActiveWorkbook.Worksheets not recognizing each worksheet and only running on the Active worksheet. Any suggestions would be great! I'm sure this is a basic fix but can't seem to figure it out. Thanks!

Sub LoopThroughWorksheets()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If (ws.Name <> "Sheet1") And (ws.Name <> "Sheet2") And (ws.Name <> "Sheet8") And (ws.Name <> "Sheet42") Then

            With ws
                'code
                'Sheets("Formula").Select
                Range("FormulaRow").Copy
                'Selection.Copy
                ActiveSheet.Select
                ActiveSheet.Range("A1").Select
                ActiveSheet.Paste
                Calculate
                Range("Q1:X1").Select
                Selection.Copy
                Range("Q3:X3000").Select
                Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
                Application.CutCopyMode = False
                Calculate
                Selection.Copy
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

            End With
        End If
    Next ws

End Sub

Upvotes: 0

Views: 6375

Answers (1)

John Bustos
John Bustos

Reputation: 19574

For a With statement, you need to proceed each time you wish to reference that object with a . to use it the way you're intending. But, if I understand your intnent, I think the easiest solution would be to add in this line of code:

ws.Select

as follows:

For Each ws In ActiveWorkbook.Worksheets
    If (ws.Name <> "Sheet1") And (ws.Name <> "Sheet2") And (ws.Name <> "Sheet8") And (ws.Name <> "Sheet42") Then

       ws.Select

        With ws
            'code
            'Sheets("Formula").Select
            Range("FormulaRow").Copy

        ...

and then you should also work on changing things such as from Range("FormulaRow").Copy to .Range("FormulaRow").Copy so you're sure to be using the With statement correctly.

Hope that makes sense...

Upvotes: 1

Related Questions