Search code examples
excelvba

Copy Formulas in Cell A13 Through K13

I have a file that has a power query in it. The query begins in Column row 13 column M and brings in a variable number of rows each month. In cell A13 through K13, I have a set of formulas that I would like to drag or copy down to the last row that is brought in by Power query in Column M each time. How would I do this?

enter image description here

Solution

  • Update Formulas Corresponding to Rows of an Excel Table (ListObject)

    • The formula used in this example is

      =COLUMN()*Table1[@Numbers]

    enter image description here

    • Imagine there are formulas only in A2:K2. PowerQuery has produced 10 data rows in the table. After running UdateFormulas, the formulas are copied down.
    • If PowerQuery now produces 5 data rows in the table, running UpdateFormulas will clear all formulas except the ones in the first row and copy them down 5 rows.
    Sub UpdateFormulas()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim lo As ListObject: Set lo = ws.ListObjects("Table1")
    If lo.ListRows.Count < 2 Then Exit Sub ' first row has already formulas
    
    ' Copy formulas and formats.
    With lo.DataBodyRange.EntireRow.Columns("A:K")
        .Resize(ws.Rows.Count - .Row).Offset(1).Clear ' keep first row
        .Rows(1).Copy .Cells
    End With

'    ' Copy only formulas (more efficient).
'    With lo.DataBodyRange.EntireRow.Columns("A:K")
'        .Resize(ws.Rows.Count - .Row).Offset(1).ClearContents ' keep first row
'        .Formula = .Rows(1).Formula ' copy as many as rows in table
'    End With

End Sub