Loop trough some sheets

[kamiljaku]

Hi,

im trying to paste some values into sheets which names are 1-12. Each of this sheets represents months of the year. Im trying the following macro

Sub wkle()

Dim i As Integer

Dim sh As Worksheet

For i = 1 To 12

ActiveSheet.Range("S46:U52").Copy

Worksheets(i).Activate
Worksheets(i).Range("S46:U52").PasteSpecial

Next i

End Sub







But Excel thinks that i is the number of the sheet and not a name. He pastes the values even if a sheet is called "input".

How to resolve it?

 

[Eric W]

You can convert it to a string so that VBA thinks it's a name:

Sheets(CStr(i)).Activate

There are a few other ways to do this.

 

[Norie]

Try this.

Sub wkle()
Dim sh As Worksheet
Dim I As Long

    For I = 1 To 12
    
        ActiveSheet.Range("S46:U52").Copy
        Set sh = Sheets(CStr(I))
        sh.Range("S46:U52").PasteSpecial

    Next I

End Sub

 

[kamiljaku]

I think this will work, although I just have sheets 1,2,10,11,12 and it shoots an error on sheet 3

How could I possibly make it go to next if one "i" is missing?

 

[Eric W]

Tweaking Norie's code a bit, either of these 2 options would work:

Sub wkle()
Dim i As Long
    
    ActiveSheet.Range("S46:U52").Copy
    On Error Resume Next
    For i = 1 To 12
        Worksheets(CStr(i)).Range("S46:U52").PasteSpecial
    Next i
End Sub

Sub wkle()
Dim i As Variant
    
    ActiveSheet.Range("S46:U52").Copy
    For Each i In Array(1, 2, 10, 11, 12)
        Worksheets(CStr(i)).Range("S46:U52").PasteSpecial
    Next i
End Sub

It just depends on whether you have the list of sheets or not.