Loop and perform action only on certain worksheets

[dsrt16]

I have a workbook with 35 worksheets, and when a button is clicked, I need a formula to be placed in cell D40 of only worksheets 4-33. The sheet names will change often, so I have to reference the sheets using the code number, and not there name.

I attempted to create the vba code, but I am rusty, and I know I was making up qualifiers that do not work.

Initially, I was going to just code each worksheet separately, but I knew I could do it with a loop. I have commented out how I was initially going to approach this.

My attempt (this is the portion of the code dealing with the loop):

 Dim ws As Worksheet, i As Integer
    
    For i = 4 To 33
        Set ws = Sheet(i)
        ws.Range("D40").Select 'this line doesn't work because I made that up :D
        ActiveCell.FormulaR1C1 = "='Weekly Setup'!R2C2"
    
    Next i
       
    'Sheet4.Range("D40").Select
    'ActiveCell.FormulaR1C1 = "='Weekly Setup'!R2C2"
    'Sheet5.Range("D40").Select
    'ActiveCell.FormulaR1C1 = "='Weekly Setup'!R2C2"
    'keep going with each sheet up to 33

 

[Misca]

Try:

Dim i As Integer

    For i = 4 To 33
        Sheets(i).Range("D40").FormulaR1C1 = "='Weekly Setup'!R2C2"
    Next i

 

[dsrt16]

That would make sense, but that also came up as an error.

I am assuming it is because I am not referencing the sheet name, but rather the sheet code number.

When referencing sheet name, you use sheets() and then the name in parenthesis, which is what that code is set up to do. The word sheets(i) seems like it is trying to result in sheets(4). And I need sheet4 to be the result since I am referencing the sheet code number.

Maybe that isn't the reason the code isn't work, but that is all I can come up with.

 

[Misca]

The Sheets(i) refers to the sheets position. If you want to refer to the specific sheet no matter where it is / what it is called you have to use the coded sheet names:

Dim i As Integer

For i = 1 To 3
    Choose(i, Sheet4, Sheet5, Sheet6).FormulaR1C1 = "='Weekly Setup'!R2C2"
Next i

Just add more sheets to your loop.

 

[dsrt16]

Referring to the sheet position would be fine. The sheet positions don't change, but the code doesn't work. I was just trying to find a reason why it didn't.

This line comes up in the debugger

Sheets(i).Range("D40").FormulaR1C1 = "='Weekly Setup'!R2C2"

and it says subscript out of range.

 

[dsrt16]

hahaha my bad. Nevermind.

It was suppose to be 3 to 32 not 4 to 33. Once I changed that, it worked. Thanks!