Solved: Copy row down based on last row of different sheet

[JimS]

Does anyone have some code that will copy an entire row down x number of rows where x is determined by the last row on a different worksheet?

In example, Sheet1 – Row 10 has several formulas across several columns that needs to be copied down the same number of rows that contain data on Sheet2. The data on Sheet2 starts in Row 2 (row 1 is a header row on Sheet2). The data on Sheet2 will vary in length each time this macro would need to run.

Thanks for any help…

JimS

[mbarron]

The following assumes the cells to be affected are in columns A through H and that the formulas you want to apply are in row 2.

The macro clears out the range from row 3 down then determines the last row with data (assuming continuous data from row 2 down in A column) on sheet2. It then fills the formulas down on Sheet1 based on that number.

Sub coopyFormula()
Dim i As Integer
Sheets("Sheet1").Range(Cells(3, "a"), Cells(3, "h").End(xlDown)).ClearContents
i = Sheets("Sheet2").Range("a1").End(xlDown).Row
If i = Rows.Count Then Exit Sub

Sheets("Sheet1").Range("A2:h2").Resize(i - 1, 8).FillDown

End Sub

[JimS]

Does not seem to work...

[lucas]

post some sample data.

Can you elaborate on what "doesn not seem to work" means?

Does it error? if so on which line and what is the error.

Does it do nothing, you know. Fill us in so we can help you.

[JimS]

Figured it out...

The Sheets("Sheet1").Range(Cells(3, "a"), Cells(3, "h").End(xlDown)).ClearContents needed to be Cells(11,"a").

Thanks for your help.