Replacing formulas in multiple sheets with the different values based on a different sheet VBA

Question

My current code creates 9 copies of a sheet called "MasterCalculator". It decides the amount of copies to be named by counting the number of cells filled in Row 1 (starting at column C) in the other sheet Called 'LLP Disc Sheet'. Each of the 9 sheets created are then named. Sheet 1's name comes from C1 in the 'LLP Disc Sheet', Sheet 2's name comes from D1 in the 'LLP Disc Sheet', Sheet 3's names comes from E1 in the 'LLP Disc Sheet', and so on.

Option Explicit

Public Sub NewSheets()
Dim shCol   As Integer
Dim i       As Integer
Dim ws      As Worksheet
Dim sh      As Worksheet
Set ws = Sheets("MasterCalculator")
Set sh = Sheets("LLP Disc Sheet")
Application.ScreenUpdating = 0
Application.EnableEvents = 0
shCol = 2
sh.Activate
For i = 2 To sh.Range("A1:Z1").Cells.SpecialCells(xlCellTypeConstants).Count
    shCol = shCol + 1
    Application.StatusBar = "Processing " & sh.Cells(1, shCol).Text & Format(i /     sh.Range("A1:Z1").Cells.SpecialCells(xlCellTypeConstants).Count, "  #0.0 %")
    Select Case shCol
    Case Is = 3
        ws.Copy After:=sh
    Case Else
        ws.Copy After:=Sheets(sh.Cells(1, shCol - 1).Text)
    End Select
    ActiveSheet.Name = sh.Cells(1, shCol).Text  
    Application.CutCopyMode = False
Next i
sh.Activate
Application.StatusBar = 0
Application.EnableEvents = 1
Application.ScreenUpdating = 1
Application.CalculateFull
End Sub

So now that all the sheets are created and named... I now want to update the formulas in each since they're copies of the sheet called 'MasterCalculator'. There are 2 cells in each sheet I need to update - cell B1 and cell M4. Cell B1 contains the formula "=+'LLP Disc Sheet'!C1". The sheet that was created based on C1 in the 'LLP Disc Sheet' can keep this formula. However, the next sheet (sheet 2) that was created and named based off of D1 in the "LLP Disc Sheet" needs to be updated to "=+'LLP Disc Sheet'!D1". This goes on with the rest of the sheets. The next has to change to =+'LLP Disc Sheet'!E1 and so on. How do I create a code to replace that cell in each of the newly created sheet with an updated formula that only changes it to cell referenced one cell after in the 'LLP Disc Sheet'?

ActiveSheet.Range(“B1:M4”).Replace_
What: ="LLP Disc Sheet'!C1", Replacement:="LLP Disc Sheet'!D1”,_    ‘but I want it to continue to the next sheet to replace D1 with E1 and so on until all of the B1 cells match their sheet names (it also allow all the data to be filled in). All of these will be found in cell B1 in the MasterCalculator copies 
What: ="LLP Disc Sheet'!$C$1:$C$", Replacement:=" LLP Disc Sheet'!$D$1:$D$”,_   ‘but I want it to continue to the next sheet to replace $D$1 with E$1$ and $D$ with $E$ and so on until all of the M4 cells are set to 0. 
SearchOrder:=xlByRows, MatchCase:=True

You dont have to ceate a code. You can change the formula in the original sheet before running macro.. You mentioned that new sheet names are in c1,d1 and so on. So the formula in B1 and M4 is nothing but the original sheet name "LLP Disc Sheet" and once you make 9 copies of the sheet you want the new names in the respective new sheet. Formula for current sheet name is =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). Enter it in B1 and M4 (assuming it is the same as B1). You wont have to change the formula for each new sheet. Formula will work once the file is saved. — Naresh

CDP1802 CDP1802 · Accepted Answer · 2020-04-02T09:35:57

Use formulaR1C1

Option Explicit

Public Sub NewSheets()

    Dim wb As Workbook, ws As Worksheet, wsMaster As Worksheet
    Dim iLastCol As Integer, iCol As Integer
    Dim s As String, n As Integer

    Set wb = ThisWorkbook
    Set wsMaster = wb.Sheets("MasterCalculator")
    Set ws = wb.Sheets("LLP Disc Sheet")
    iLastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

    n = wb.Sheets.Count
    For iCol = 3 To iLastCol
        s = ws.Cells(1, iCol) ' sheet name
        If Len(s) > 0 Then
            wsMaster.Copy After:=Sheets(n)
            n = n + 1
            wb.Sheets(n).Name = s
            wb.Sheets(n).Range("B1,M4").FormulaR1C1 = "='" & ws.Name & "'!R1C" & iCol
        End If
    Next

    MsgBox iLastCol - 2 & " sheets added", vbInformation

End Sub

Replacing formulas in multiple sheets with the different values based on a different sheet VBA

1 Answers