Macro to update inconsistent column formula in last row of table after adding new row(s)

[captainxcel]

Hi,
I run a loop macro to increase the number of rows in 25+ client tables when a new ticker symbol is added. Unfortunately, some columns in each table have gone through iterations of formulas and the new cells in those columns are populating with old formulas when new row(s) are added. The manual fix for this is to F2 in the topmost cell in each table column where this is an issue, copy the formula, escape, select the whole column, clear the contents of all cells in that table column, then f2 back into the cell, paste the formula, end hit enter. Would there be a VBA solution to this as I am going through about 5 columns in each table.
Thanks!

 

[JackDanIce]

If you're running a loop macro to increase client tables, can't you use a loop macro to the 5 columns?
Below will copy F2 and apply to the last row in F:

Sub UpdateFormula()
      
    With Cells(2, 6).Resize(Application.Max(1, Cells(Rows.Count, 6).End(xlUp).Row))
        .Cells(1, 1).Copy
        .PasteSpecial xlPasteFormulas
    End With
   
End Sub

 

[captainxcel]

If you're running a loop macro to increase client tables, can't you use a loop macro to the 5 columns?
Below will copy F2 and apply to the last row in F:

Sub UpdateFormula()
     
    With Cells(2, 6).Resize(Application.Max(1, Cells(Rows.Count, 6).End(xlUp).Row))
        .Cells(1, 1).Copy
        .PasteSpecial xlPasteFormulas
    End With
  
End Sub

Thank you. I actually meant that I was using F2 (the function keys) to enter the cell in order to copy the formula.

 

[JackDanIce]

This will fail if there are no formulas in any cell in row 2, otherwise try:

Sub UpdateFormula()
   
   Dim x As Long: x = Application.Max(1, Cells(Rows.Count, 1).End(xlUp).Row - 1)
   Dim c As Long: c = Cells(2, Columns.Count).End(xlToLeft).Column
   
   Application.ScreenUpdating = False
   
   For Each r In Cells(2, 1).Resize(, c).SpecialCells(xlFormulas)
        With r
            .Offset(1).Resize(x - 1).Value = ""
            .NumberFormat = "General"
            .Copy
            .Resize(x).PasteSpecial xlPasteFormulas
         End With
         Application.CutCopyMode = False
    Next r
   
    Application.ScreenUpdating = True
    
End Sub

 

[captainxcel]

Thanks Jack. I'll try this out today and report back. Many thanks.

 

[captainxcel]

This worked well for me. Changed the row from 2 to the row where my table formulas are and it worked! Thanks Jack. Very elegant and efficient code.

 

[JackDanIce]

Glad it's resolved, thanks for the feedback