Excel VBA iterate through sheets and change cells to reflect summary sheet

Question

So I am new to VBA and trying to write a piece of code to increase my efficiency. I am trying to iterate through 66 different sheets (named 1, 2, 3, etc.) and change the values of certain cells to reflect values listed in a summary sheet. I want to essentially iterate down by one row in the summary sheet for every sheet in the workbook. I tried to record a macro and write something based on that, but my solution doesn't work. Any help would be appreciated!

*Updated code, but something still isn't working. I think it may be because I am not properly selecting the sheet that I want to make the changes in?

**Edit2: The code works now- I guess I had to put the first 66 sheets at the beginning of the workbook, the macro was trying to iterate through other protected sheets. Thanks for the help.

Sub Macro6()
Dim WS_Count As Integer
Dim i As Integer
'Set WS_Count to the number of sheets with CPT data
WS_Count = 66
For i = 1 To WS_Count
Sheets(i).Activate
Sheets(i).Select
Range("A2").FormulaR1C1 = "='Data Input'!R[" & (17 + i) & "]C[1]"
Range("F6").FormulaR1C1 = "='Data Input'!R[" & (14 + i) & "]C[4]"
Range("H6").FormulaR1C1 = "='Data Input'!R[" & (14 + i) & "]C[3]"
Range("I6").FormulaR1C1 = "='Data Input'!R[" & (14 + i) & "]C[3]"
Range("L6").FormulaR1C1 = "='Data Input'!R[" & (14 + i) & "]C[8]"
Range("M6").FormulaR1C1 = "='Data Input'!R[" & (14 + i) & "]C[9]"
Next
End Sub

Tim Williams Tim Williams · Accepted Answer · 2016-02-25T17:27:26

Range("A2").FormulaR1C1 = "='Data Input'!R["& (17+i) & "]C[1]"

Etc.

No need to select the cells before adding the formula

EDIT: try not using RC notation (you might need to adjust the numbers a little)

Sub Macro6()
    Const WS_Count As Long = 66 'number of sheets with CPT data
    Dim i As Long

    For i = 1 To WS_Count
        With Sheets(i)
            .Range("A2").Formula = "='Data Input'!A" & (17 + i)
            .Range("F6").Formula = "='Data Input'!D" & (14 + i)
            .Range("H6").Formula = "='Data Input'!C" & (14 + i)
            .Range("I6").Formula = "='Data Input'!C" & (14 + i)
            .Range("L6").Formula = "='Data Input'!H" & (14 + i)
            .Range("M6").Formula = "='Data Input'!I" & (14 + i)
        End With
    Next
End Sub

Excel VBA iterate through sheets and change cells to reflect summary sheet

1 Answers