Loop through Range of sheets

Question

I am trying to loop through a range of sheets. However, I get an error when selecting the sheets from a range on a sheet.

Sub Calc_sheets()

Dim sheetnames As Variant
sheets("Calc_sheets").Select
'sheetnames = Worksheets("Calc_sheets").Range("a2:a4").Value # returns an error in row "Sheet = sheetnames(k)"

sheetnames = Array("Sheet1", "Sheet2") # works fine

For k = 0 To Application.CountA(sheetnames) - 1

        Sheet = sheetnames(k)
        sheets(Sheet).Select
        ActiveSheet.Calculate
Next k

End Sub

Do you know how to solve this?

Best,

Untested, but I think you just need to say, `Set sheetnames = Worksheets...` — jcarroll, Mar 14 '18 at 13:47
Not a good idea to use `.Select` and `ActiveSheet.` anyway. See [How to avoid using Select in Excel VBA macros - Stack Overflow](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — Vincent G, Mar 14 '18 at 13:50
What's is your array base? If the array is `sheetnames(1)="Sheet1": sheetnames(2)="Sheet2"` then `sheetnames(0)` will return an error. Use `Lbound` and `Ubound` to check — Chronocidal, Mar 14 '18 at 13:53

Scott Craner · Answer 1 · 2018-03-14T17:35:28.337

3

Use Lbound and Ubound to loop through the array.

Declare the sht as a worksheet and Set the sheet. Then avoid the use of select and activate.

Sub Calc_sheets()

Dim sheetnames As Variant
Dim sht as Worksheet


sheetnames = Array("Sheet1", "Sheet2")

For k = LBound(sheetnames) to Ubound(sheetnames)

        Set Sht = worksheets(sheetnames(k))
        sht.Calculate
Next k

End Sub

edited Mar 14 '18 at 17:35

answered Mar 14 '18 at 13:48

Scott Craner

148,073
10
49
81

1

`shet` ? not `sht ? you were trying to type so fast ;) – Shai Rado Mar 14 '18 at 13:53
5

@ShaiRado The purpose of this macro is calculation. It's all about getting shet done. – Chronocidal Mar 14 '18 at 13:54
Great. How can I pull the sheetnames form a Range from a sheet? – Peter Mar 14 '18 at 15:46
Sheetnames = worksheets ("sheet3").range("A1:A4").value @AlexKlindt – Scott Craner Mar 14 '18 at 15:53
This returns Runtime error 9 for me - in line `Set sht = Worksheets(sheetnames(k))` – Peter Mar 14 '18 at 17:34
I removed the "note" but that would not have caused the error. That error is because you have misspelled the sheet names. Double check the sheet names and that the names do not have spaces or other unprintable characters not accounted for in your strings. @AlexKlindt – Scott Craner Mar 14 '18 at 17:36

DisplayName · Answer 2 · 2018-03-14T13:58:10.020

you have to use Set

Dim Sheet AS Worksheet
Set Sheet = sheetnames(k)

but you can shorten it down to

Sub Calc_sheets()
    Dim sheetnames As Variant
    sheetnames = Array("Sheet1", "Sheet2")

    Dim Sheet As Variant
    For Each Sheet In sheetnames
        Sheets(Sheet).Calculate
    Next
End Sub

or even shorter:

Sub Calc_sheets()
    Dim Sheet As Variant
    For Each Sheet In Array("Sheet1", "Sheet2")
        Sheets(Sheet).Calculate
    Next
End Sub

Vityata · Answer 3 · 2018-03-14T14:01:57.097

1

Sub CalcSheets()

    Dim k           As Long
    Dim sheetnames  As Variant

    sheetnames = Array("Sheet1", "Sheet2")

    For k = 0 To Application.CountA(sheetnames) - 1
        Sheets(sheetnames(k)).Calculate
    Next k    
End Sub

In general, Select and ActiveSheet should be avoided and if you are using .Calculate explicitly something should be quite wrong in the whole worksheet design.

edited Mar 14 '18 at 14:01

answered Mar 14 '18 at 13:48

Vityata

42,633
8
55
100

Loop through Range of sheets

3 Answers3