Copying range to variable number of rows
I have a number of worksheets (Sheets 1 to 4) with formulas in A2:J2.
These formulas need to be copied down to however many rows are populated in Column A of another Sheet called Data. So, if I import 140 rows of data into the Data sheet and run the macro the formulas in Sheets 1 to 4 get copied from row 2 down to row 140, if I import 200 rows the formulas get copied to row 200 etc. I'm struggling with this - probably because I'm using a variable (number of rows) in another sheet rather than the equivalent of a shift-end-down-right command. Any help would be much appreciated Thank you |
Copying range to variable number of rows
Hi nospaminlich
Try this This example will filldown A2:J2 on "Sheet1" to row (row with last value in Sheets "Data" in column A) Sub test() Dim LastRow As Long With Worksheets("Sheet1") LastRow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row .Range("A2:J2").AutoFill Destination:=.Range("A2:J" & LastRow) _ , Type:=xlFillDefault End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "nospaminlich" wrote in message ... I have a number of worksheets (Sheets 1 to 4) with formulas in A2:J2. These formulas need to be copied down to however many rows are populated in Column A of another Sheet called Data. So, if I import 140 rows of data into the Data sheet and run the macro the formulas in Sheets 1 to 4 get copied from row 2 down to row 140, if I import 200 rows the formulas get copied to row 200 etc. I'm struggling with this - probably because I'm using a variable (number of rows) in another sheet rather than the equivalent of a shift-end-down-right command. Any help would be much appreciated Thank you |
Copying range to variable number of rows
That's brilliant Ron. Many thanks.
|
All times are GMT +1. The time now is 02:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com