• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dim worksheets as array

H

Herbalgiraffe

New Member
Hey guys,

I need to perform a loop on a series of worksheets, but I am getting type mismatch error for the following:

Code: 
Dim YTD As Variant
Dim wks As Worksheet

Set wks = ActiveSheet
Set YTD = Array("Sheet11", "Sheet13", "Sheet15", "Sheet4", "Sheet2", "Sheet5", "Sheet7", "Sheet9")
 
You can do something like:
Code: 
Set YTD = Sheets(Array("Sheet11", "Sheet13", "Sheet15", "Sheet4", "Sheet2", "Sheet5", "Sheet7", "Sheet9"))
For Each sht In YTD
  'do something with sht, which is a sheet.
Next sht
 
That worked! thank you. Now I am faced with another problem. Forgive me as I am a new ambitious vba student, but now when I test the code it says:

"Compile error: for each may only iterate over a collection object or an array"

The overall function of this is to update two different sets of pivot tables to differing dates depending on whether or not the report is showing a year's worth of data or a month's worth of data. Additionally, it highlights "YTD" at the beginning of the first for each loop when the error comes up for some reason even if my cursor isn't there, but I already declared that variable, so I am unsure why it would trigger this error. You guys are awesome! all of these pivot tables carry the same source date filter as well if that matters.

Code: 
Sub RefreshPT()

    Dim wks As Worksheet
    Dim pt As PivotTable
    Dim YTD As String
    Dim MTD As String
    Dim YearStart As Date
    Dim MonthStart As Date
    Dim Today As Date
    Dim EOLastMonth As Date
    Set wks = ActiveSheet
    
    YTD = Array("Sheet11", "Sheet13", "Sheet15", "Sheet4", "Sheet2", "Sheet5", "Sheet7", "Sheet9")
    
    MTD = Array("Sheet10", "Sheet12", "Sheet14", "Sheet16", "Sheet3", "Sheet6", "Sheet8")
    
    YearStart = Range("A41").Value
    MonthStart = Range("B41").Value
    Today = Range("C41").Value
    EOLastMonth = Range("D41").Value
    
    For Each wks In YTD
        For Each pt In wks.PivotTables
            pt.PivotFields("Date").ClearAllFilters
            pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=YearStart, Value2:=Today
        Next pt
    Next wks
    
    
    For Each wks In MTD
        For Each pt In wks.PivotTables
            pt.PivotFields("Date").ClearAllFilters
            pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=MonthStart, Value2:=EOLastMonth
        Next pt
    Next wks
    
End Sub
 
P45scal thank you. Now that I have adjusted that,, I am getting a "runtime error 9, sub script out of range" error on the YTD array line, and when I hover over the code a window pops up showing that YTD = Empty. How could this be if it is declared as variable and has been given the array value? Below is the current code.

Code: 
Option Explicit

Sub RefreshPT()

    Dim wks As Worksheet
    Dim pt As PivotTable
    Dim YTD As Variant
    Dim MTD As Variant
    Dim YearStart As Date
    Dim MonthStart As Date
    Dim Today As Date
    Dim EOLastMonth As Date
    Set wks = ActiveSheet
    
    YTD = Sheets(Array("Sheet11", "Sheet13", "Sheet15", "Sheet4", "Sheet2", "Sheet5", "Sheet7", "Sheet9"))
    
    MTD = Sheets(Array("Sheet10", "Sheet12", "Sheet14", "Sheet16", "Sheet3", "Sheet6", "Sheet8"))
    
    YearStart = Range("A41").Value
    MonthStart = Range("B41").Value
    Today = Range("C41").Value
    EOLastMonth = Range("D41").Value
    
    For Each wks In YTD
        For Each pt In wks.PivotTables
            pt.PivotFields("Date").ClearAllFilters
            pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=YearStart, Value2:=Today
        Next pt
    Next wks
    
    
    For Each wks In MTD
        For Each pt In wks.PivotTables
            pt.PivotFields("Date").ClearAllFilters
            pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=MonthStart, Value2:=EOLastMonth
        Next pt
    Next wks
    
End Sub
 

Attachments

  • 2020-02-26_14-06-54.png
    2020-02-26_14-06-54.png
    67.1 KB · Views: 1
Since you used quote marks I took it you were referring to the sheets' names as on their tabs, but your picture shows you're using the codenames of these sheets.
So try:
Code: 
YTD = Array(Sheet11, Sheet13, Sheet15, Sheet4, Sheet2, Sheet5, Sheet7, Sheet9)
For Each wks In YTD
'etc.
 
P45cal, Thank you again for helping, I should have known better on that one. Now that I have adjusted that it is giving a run-time error 13, type mismatch on the same line. What would an array of worksheets be stored as outside of a variant? Hovering over YTD it is still showing as = Empty. Thank you again so much for your patience with this, it is only a matter of time until I get it but learning it all is a lot to remember at first.

Code: 
Sub RefreshPT()

    Dim wks As Worksheet
    Dim pt As PivotTable
    Dim YTD As Variant
    Dim MTD As Variant
    Dim YearStart As Date
    Dim MonthStart As Date
    Dim Today As Date
    Dim EOLastMonth As Date
    Set wks = ActiveSheet
    
    YTD = Sheets(Array(Sheet11, Sheet13, Sheet15, Sheet4, Sheet2, Sheet5, Sheet7, Sheet9))
    
    MTD = Sheets(Array(Sheet10, Sheet12, Sheet14, Sheet16, Sheet3, Sheet6, Sheet8))
    
    YearStart = Range("A41").Value
    MonthStart = Range("B41").Value
    Today = Range("C41").Value
    EOLastMonth = Range("D41").Value
    
    For Each wks In YTD
        For Each pt In wks.PivotTables
            pt.PivotFields("Date").ClearAllFilters
            pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=YearStart, Value2:=Today
        Next pt
    Next wks
    
    
    For Each wks In MTD
        For Each pt In wks.PivotTables
            pt.PivotFields("Date").ClearAllFilters
            pt.PivotFields("Date").PivotFilters.Add Type:=xlDateBetween, _
        Value1:=MonthStart, Value2:=EOLastMonth
        Next pt
    Next wks
    
End Sub
 
Again:
YTD = Sheets(Array(Sheet11, Sheet13, Sheet15, Sheet4, Sheet2, Sheet5, Sheet7, Sheet9))
 
It worked! Sorry for missing that the first time you wrote it. You guys are seriously life savers. I have some other issues to deal with related to the source data I am working with and not the vba code, so as far as this is concerned the matter is solved. Thanks again! any tips or resources for a new learner you guys recommend to help me learn? I am obviously using forums and also got the VBA for dummies book, but I was wondering what helped you guys when you were first learning.
 
You must log in or register to reply here.
Back
Top