0

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
CDay
  • 99
  • 1
  • 10
  • 1
    Probably helpful to read [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), which has good principles to apply to avoid `Actitvate` as well. – BigBen Apr 17 '20 at 12:39
  • Most likely the body of the macro doesn't reference `With ws(Report(i))`. – BigBen Apr 17 '20 at 12:40
  • 1
    Your For Loop looks wrong. Are you sure it's `unbound` and not `UBound`? – Kevin Mueller Apr 17 '20 at 12:40
  • Also perhaps you mean `Set ws = Worksheets(Report(i))` ? and remove the with line? – SJR Apr 17 '20 at 12:41
  • @BigBen I do try to avoid using Select and Activate. However, when I open the report, I'm on Sheet1 (TOC). I need the macro to run on Sheet1, and then loop through Sheet5, Sheet8, Sheet11, Sheet14. If I don't `Activate` Sheet2, it tries to run the macro on Sheet1. How do I fix that? – CDay Apr 21 '20 at 14:20
  • @CariDay - then you need to qualify the worksheet any `Range` object is on. – BigBen Apr 21 '20 at 14:21
  • @BigBen When you say qualify the worksheet any Range? I don't understand.. I was going to try to use Applecore's answer below. I currently have `Dim wb As Workbook: Set wb = ActiveWorkbook Dim ws As Worksheet Dim LstObj As ListObjects Dim LastRow As Long Dim aReport As Variant Dim lngLoop1 As Long aReport = Array("Report1", "Report4", "Report7", "Report10", "Report13") For lngLoop1 = LBound(aReport) To UBound(aReport) Set ws = Worksheets(aReport(lngLoop1)) With ws` – CDay Apr 21 '20 at 14:24
  • What matters is what comes *after* `With ws`, i.e. most likely you're not actually referencing the `ws`. – BigBen Apr 21 '20 at 14:26
  • Hmmm.. so, If I want to loop through specific worksheets, I have: `Dim aReport As Variant Dim lngLoop1 As Long aReport = Array("Report1", "Report4", "Report7", "Report10", "Report13") For lngLoop1 = LBound(aReport) To UBound(aReport) Set ws = Worksheets(aReport(lngLoop1)) With ws` And then I start the macro to create a table on worksheet2. What should I be noting after `With ws`? – CDay Apr 21 '20 at 14:31

1 Answers1

1

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,

Applecore
  • 3,934
  • 2
  • 9
  • 13
  • `Report` isn't a member of the object model, it's safe to use. – BigBen Apr 17 '20 at 12:50
  • 1
    It isn't in the Excel VBA library, but it is in the Access VBA library, so I'm trying to "futureproof"!! – Applecore Apr 17 '20 at 12:54
  • @Applecore I copied the coding you suggested, but it's not looping to the next report. It's trying to loop through "Report1" again. – CDay Apr 21 '20 at 14:37
  • @CariDay - I've just run this with `Debug.Print` to get `.Cells(1,1)` inside the `With ws...End With`, and it correctly returns the different values for the two worksheets that I have set up in the array. There must be something else that is happening to cause it to re-loop the first element. – Applecore Apr 21 '20 at 14:45
  • @Applecore I think it might be the 'Insert Table portion that occurs right after `With ws`. I currently have: `'Insert Table Range("A3").CurrentRegion.Select ActiveSheet.ListObjects.Add(xlSrcRange, Selection.CurrentRegion, , xlYes).Name = "Table1"` – CDay Apr 21 '20 at 15:06
  • 1
    Do not mix Select or Activate with code that change the current sheet. Better, never use select or activate in vba. a) The code snippet of @Apple core loops through the sheets but it do not change the current sheet. b) Your code snippet refers to the current sheet. a & b => bug – hpchavaz May 03 '20 at 13:28