CODEWITHSUNDEEP
excelvba
CDay
CDay

Reputation: 111

VBA set a Loop for specific worksheets in a workbook

I run a monthly report that generates 16 tabs (15 worksheets: "Report1" - "Report15"). I've created a sub to create/format a table, and organize the data on Sheet2("Report1").

Objective: Because of the Table Style, I would now like to loop the macro through "Report1", "Report4", "Report7", "Report10", "Report13" Only.

(Once I figure this out, I'll create a Macro with another Table Style for the other worksheets.)

Issues: Through 'Googling' I created the below Loop, but the "Set ws = Worksheets(Report1") ws.active is throwing it off. -Do I need to remove the set ws = worksheets(Report1")?

-I had the ws.active, because the macro didn't seem to work without it.

Macro:

Option Explicit

Sub LoopThroughSpecificWorksheets()

'Turn Off Screen Updates
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim ws As Worksheet
    Dim LstObj As ListObjects
    Dim LastRow As Long
    Dim Report, i

    Report = Array("Report1", "Report4", "Report7", "Report10", "Report13")

    For i = LBound(Report) To unbound(Report)
        With ws(Report(i))

    Set ws = Worksheets("Report1")
    ws.Activate

'...Body of Maco
'Insert Table
'Remove Table Format
'Apply Tablestyle:
'Apply a filter to $ Share for all Brands (Largest to Smallest)
'Update $ - % Chg formula
'Update Units - % Chg Formula
'Change Header Names and Resize


    End With
Next i

'Turn On Screen Updates
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Upvotes: 0

Views: 1221

Answers (1)

Applecore
Applecore

Reputation: 4099

Below is some VBA code that loops your array of worksheets:

Sub sLoopArray()
    Dim ws As Worksheet
    Dim aReport As Variant
    Dim lngLoop1 As Long
    aReport = Array("Report1", "Report2")
    For lngLoop1 = LBound(aReport) To UBound(aReport)
        Set ws = Worksheets(aReport(lngLoop1))
        With ws


        End With
    Next lngLoop1
End Sub

I've changed the type of unbound to UBound, declared the loop counter as Long (you had it as a variant which can cause problems), and also renamed the array from Report to aReport (to avoid "collision" with any inbuilt VBA names.

Regards,

Upvotes: 1

Related Questions