I have the following code, and I want to overwrite the formulas in cells S11 to S13 with these three formulas:

='https://cloud.sharepoint.com/Shared Documents/[New York.xlsx]SHEETNAME(1)'!$Y11

='https://cloud.sharepoint.com/Shared Documents/[New York.xlsx]SHEETNAME(1)'!$Y12

=SUM(S12:S13)

    Sub update_formulas()'
    '
    '
        Dim i As Integer
        Dim VarCellValue As String
        Dim VarCellValue2 As String
        Dim VarCellValue3 As String
        Dim currwbk As Workbook
    
        Set currwbk = ActiveWorkbook

' This contains a range of spreadsheet file names to open

      For i = Range("A2").Value To Range("C2").Value    
         Application.DisplayAlerts = False 
         Workbooks.Open (Range("A3").Value & VarCellValue & "\" & VarCellValue & ".xlsm"), UpdateLinks:=3
         Range("S11").Select
         Sheets("SHEET 1").Select 'I want this to be dynamic but select the first tab in each sheet

'Here is where the code will go. Currently it selects from another sheet, but I'd like to pull it straight out of the module.

          Range("S12:S14").PasteSpecial Paste:=xlPasteFormulas

      Next i
        
    End Sub

CodePudding user response：

• Just write the formulas directly.
• Use a Workbook variable.
• Make sure to fully qualify any Range call. For example, you create a filepath with Range("A3").Value & VarCellValue & "\" & VarCellValue & ".xlsm". The Range("A3") is unqualified, meaning that its parent worksheet and parent workbook are unspecified, and are implicitly ActiveSheet/ActiveWorkbook.

Dim wb As Workbook
Set wb = Workbooks.Open(Range("A3").Value & VarCellValue & "\" & VarCellValue & ".xlsm", UpdateLinks:=3) ' note the placement of parentheses

With wb.Worksheets(1)
   .Range("S12").Formula = "='https://cloud.sharepoint.com/Shared Documents/[New York.xlsx]SHEETNAME(1)'!$Y11"
   .Range("S13").Formula = "='https://cloud.sharepoint.com/Shared Documents/[New York.xlsx]SHEETNAME(1)'!$Y12"
   .Range("S14").Formula = "=SUM(S12:S13)"
End With