VBA Loop through worksheets and update formula

[Giggs1991]

Hi All,

I have a list of worksheet names in "Sheet1" range B2:B100.

I would like to paste the formula : ActiveCell.FormulaR1C1 = "=CONCATENATE(Sheet1!R[1]C[1],""C25"")" into cell A1 of each worksheet mentioned in column B of Sheet1. However, the concatenate formula should keep updating for each worksheet.


For example,
For the first worksheet, the the vba should update the formula as : ActiveCell.FormulaR1C1 = "=CONCATENATE(Sheet1!R[1]C[2],""C25"")" in cell A1

For the 2nd worksheet, the the vba should update the formula as : ActiveCell.FormulaR1C1 = "=CONCATENATE(Sheet1!R[2]C[2],""C25"")" in cell A1

For the 3rd worksheet, the the vba should update the formula as : ActiveCell.FormulaR1C1 = "=CONCATENATE(Sheet1!R[3]C[2],""C25"")" in cellA1

Please help

 

[Trebor76]

Hi Giggs1991,

I think this is what you're after:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim lngLastRow As Long
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
    
    For Each rngMyCell In ThisWorkbook.Sheets("Sheet1").Range("B2:B" & lngLastRow)
        On Error Resume Next
            Set ws = ThisWorkbook.Sheets(CStr(rngMyCell))
            If Not ws Is Nothing Then
                ws.Range("A1").Formula = "=CONCATENATE(Sheet1!" & rngMyCell.Address & ",""C25"")"
            End If
            Set ws = Nothing
        On Error GoTo 0
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert