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: stackoverflow.com/questions/10714251/… — Scott Craner
– Scott Craner, Commented Oct 7, 2016 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
– Preston, Commented Oct 7, 2016 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
– Matt Cremeens, Commented Oct 7, 2016 at 14:47
I believe sheet indexing starts at one, so you will need to changeindexVal=0 to indexVal=1 and go from there. — Matt Cremeens
– Matt Cremeens, Commented Oct 7, 2016 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
– Comintern, Commented Oct 7, 2016 at 14:53

Scott Craner · Accepted Answer · 2016-10-07 15:04:53Z

2

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

edited Oct 7, 2016 at 15:04

answered Oct 7, 2016 at 14:52

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Comintern Over a year ago

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...

Zac · Accepted Answer · 2016-10-07 14:47:08Z

0

You are referring to Worksheetss, it should be Worksheets

answered Oct 7, 2016 at 14:47

Zac

1,9501 gold badge10 silver badges23 bronze badges

3 Comments

Matt Cremeens Over a year ago

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.

Scott Craner Over a year ago

I had this as a comment that I deleted, because Worksheetss is a string array.

Zac Over a year ago

:) 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

Shai Rado · Accepted Answer · 2016-10-07 15:12:12Z

0

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

edited Oct 7, 2016 at 15:12

answered Oct 7, 2016 at 14:54

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Comments

Kristian Nielsen · Accepted Answer · 2016-10-07 15:46:45Z

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 7, 2016 at 15:46

answered Oct 7, 2016 at 15:27

Kristian Nielsen

1491 silver badge12 bronze badges

1 Comment

Shai Rado Over a year ago

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

Collectives™ on Stack Overflow

Error when trying to loop through multiple sheets VBA

4 Answers 4

1 Comment

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related