CODEWITHSUNDEEP
excelvba
user2998195
user2998195

Reputation: 13

Macro to Copy and Paste down Variable Number or Rows from Record Count on Second Tab

I have an Excel workbook with 2 tabs: "Data", and "Report". Each tab has a header row (row 1).

Every time I run a new report, I'll paste new data onto the "Data" tab. The "Report" tab includes some formulas that reference data on the "Data" tab. I want to be able to create a Macro to copy-down the formulas on the "Report" tab to the corresponding number of rows from the "Data" tab. (The number of rows will vary each time I run the report).

For simplicity sake, let's say on the "Report" tab, I have a header row and the some formulas in A2 & B2. I want to be able to run a Macro that will copy-down these formulas for the corresponding number of rows to the row count for the "Data" tab.

I've found the following code - but am not sure how to modify it to meet my needs:

Sub CopyValueDown() Dim lRow As Integer

Sheets("Data").Select
lRow = Range("A" & Rows.Count).End(xlUp).Row
If lRow = 1 Then GoTo End
Range("A1:B1").AutoFill Destination:=Range("A1:B" & lRow)
End Sub

Any suggestions?

Upvotes: 1

Views: 11542

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Sub CopyValueDown() 

Dim lRow As Integer

    lRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    If lRow > 1 Then 
        Sheets("Report").Range("A2:B2").AutoFill _
                  Destination:=Range("A2:B" & lRow)
    end if
End Sub

Upvotes: 1

Related Questions