Auto fill rows based on data source

[jimbomcmucka]

Hi Guys, hoping you can help?

I am producing a report, extracting and presenting simplified data from a system extract. I am extracting a .csv file from the database and then pasting it into a tab named 'data'. I am then using a tab named 'Report' to show a simplified key data. The data source will differ in length with every extract. I was hoping there may be a way to automate populating the formulas that appear in Row 2 into the correct number of rows to match the data source. e.g. Todays extract contains 821 rows of data. So I would like to automate the equivalent of selecting row 2 and dragging it down to row # 822.

Can anybody advise of the best way to do this?

Many thanks,
Jim

 

[Herakles]

Put this into a standard code module.

For each column in the Report worksheet it copies down the formula in row 2 if there is one.

Public Sub subCopyFormulasDown()
Dim WsData As Worksheet
Dim WsReport As Worksheet
Dim rng As Range
Dim lngRows As Long

    Set WsData = Worksheets("Data")
    Set WsReport = Worksheets("Report")
    
    lngRows = WsData.Range("A1").Range("A" & Rows.Count).End(xlUp).Row
    
    For Each rng In WsReport.Range("A1").CurrentRegion.Rows(1).Cells
        If rng.Offset(1, 0).HasFormula = True Then
            rng.Offset(2, 0).Resize(lngRows - 2, 1).Formula = rng.Offset(1, 0).Formula
        End If
    Next rng
    
End Sub

 

[jimbomcmucka]

Amazing! Thanks so much.