Error when trying to loop through multiple sheets VBA

Question

I have written this code to loop though multiple sheets and perform a certain task, but for some reason I'm told that Subscript is out of range at this line:

Sheets(Worksheetss(indexVal)).Range("B4:S25").Select

The complete code can be seen here:

Sub kopierforsatan()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False

Dim Worksheet As Variant
Dim Worksheetss As Variant 
Dim outputs As Variant

Worksheetss = Array("6_år_lav", "6_år_middel", "6_år_høj", "10_år_høj")


Dim indexVal As Integer
indexVal = 0

For Each Worksheet In Worksheetss


Sheets(Worksheetss(indexVal)).Range("B4:S25").Select
Application.CutCopyMode = False
Selection.Copy

Sheets(Worksheetss(indexVal)).Range("V4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy

Sheets(Worksheetss(indexVal)).Range("V30").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
Sheets(Worksheetss(indexVal)).Range("B54").Select
Sheets(Worksheetss(indexVal)).Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets(Worksheetss(indexVal)).Range("V50").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
Application.CutCopyMode = False
Selection.Copy

Sheets(Worksheetss(indexVal)).Range("Y30").Select
ActiveSheet.Paste

indexVal = indexVal + 1
Next Worksheet


End Sub

What troubles me the most, is that I have used this syntax before with respect to the way the for each loop is constructed.

I Hope someone can see where I have made an error.

And just an FYI, Using Select and Activate slows down the code and in general is not needed. This discussion points out many good methods on how to avoid using Select and Activate: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Scott Craner, Oct 07 '16 at 14:41
Scott beat me to it. Just a note to say don't forget to turn screen updating etc on before you end the sub! — Preston, Oct 07 '16 at 14:44
`Worksheet` is a reserved word. I think you need to use something else, like `ws`, as in `For Each ws in Worksheetss`. — Matt Cremeens, Oct 07 '16 at 14:47
I believe sheet indexing starts at one, so you will need to change`indexVal=0` to `indexVal=1` and go from there. — Matt Cremeens, Oct 07 '16 at 14:51
@MattCremeens - They're being indexed by name. `indexVal` is the index for the `Worksheetss` array. I'm guessing this is an issue with using the global `Sheets` object. — Comintern, Oct 07 '16 at 14:53
@Comintern Ah, you're right. I'm guessing Scott's answer below will take care of him. Thanks for pointing out my misstatement. — Matt Cremeens, Oct 07 '16 at 14:55
@tompreston - the screenupdating setting is not persistent: it will reset as soon as the sub ends — Tim Williams, Oct 07 '16 at 16:19

Scott Craner · Accepted Answer · 2016-10-07T15:04:53.103

See @comintern's comment It's a horrible idea to use them for variable names, but VBA determines the context from scope and usage. If you have Dim Worksheets As Variant it just means that you have to qualify ThisWorkbook.Worksheets. Again, not the best idea. as to why one should not use worksheet as a variable.

Also try avoid using Select.

Third when using a for loop many times a counter is not needed. Just refer to the variable you created.

Sub kopierforsatan()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False

Dim ws As Variant
Dim Worksheetss As Variant
Dim outputs As Variant

Worksheetss = Array("6_år_lav", "6_år_middel", "6_år_høj", "10_år_høj")


For Each ws In Worksheetss
    With Sheets(ws)
        .Range("B4:S25").Copy
        .Range("V4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Range("V30").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        .Range(.Range("B54"), .Range("B54").End(xlDown)).Copy .Range("V50")
        .Range(.Range("B54"), .Range("B54").End(xlDown)).Copy .Range("Y30")
    End With
Next ws

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True

End Sub

`Worksheets` and `Worksheet` aren't "reserved" names. COM objects, including Excel can't "reserve" anything. It's a horrible idea to ***use*** them for variable names, but VBA determines the context from scope and usage. If you have `Dim Worksheets As Variant` it just means that you have to qualify `ThisWorkbook.Worksheets`. Again, not the best idea... — Comintern, Oct 07 '16 at 15:02

score 0 · Answer 2 · answered Oct 07 '16 at 14:47

0

You are referring to Worksheetss, it should be Worksheets

answered Oct 07 '16 at 14:47

Zac

1,924
1
8
21

2

I thought that too at first, but it turns out that `Worksheetss` is an array in his case. I agree that it is probably a poor naming convention, but I doubt it's the source of the OP's error. – Matt Cremeens Oct 07 '16 at 14:49
I had this as a comment that I deleted, because Worksheetss is a string array. – Scott Craner Oct 07 '16 at 14:50
:) you are both correct. @Kristian, that's probably not a good naming convention. Also, why not just use `For Each oWS In ThisWorkbook.Worksheets` where `oWS` is `Dim oWS as Worksheet` – Zac Oct 07 '16 at 14:53

Shai Rado · Answer 3 · 2016-10-07T15:12:12.690

You can loop through all Worksheets in your Workbook, and then just check if the current sheet's name equals to the one in your array.

(I prefer using a Variable named Sht and not something close to `Worksheet1).

Dim Sht     As Worksheet       

For Each Sht In ThisWorkbook.Sheets
    With Sht
        Select Case .Name
            Case "6_år_lav", "6_år_middel", "6_år_høj", "10_år_høj"
                Application.CutCopyMode = False
                .Range("B4:S25").Copy
                .Range("V4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                ' etc... continue the rest of your copy > Paste            

        End Select
    End With     

Next Sht

Kristian Nielsen · Answer 4 · 2016-10-07T15:46:45.700

0

Thank you so much for all your commments and answers, and then that fast!

I managed to implement a solution using @Scott Craners answer with a little modification. The modification was only necessary because I need to have the vector created from B54 transposed.

I will have in mind what you guys has discussed about convention with respect to names of variables, and have a look at the link about using select and activate.

Thank you so much for your time.

Have a great weekend.

edited Oct 07 '16 at 15:46

answered Oct 07 '16 at 15:27

Kristian Nielsen

159
1
11

1

this is not the way SO operates, since you are using @Scott Craner solution , then mark his answer as "Answer", not post his solution as your own – Shai Rado Oct 07 '16 at 15:33

Error when trying to loop through multiple sheets VBA

4 Answers4