Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying range to variable number of rows
That's brilliant Ron. Many thanks.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a variable range of data from one worksheet to another. | Excel Discussion (Misc queries) | |||
Macro for copying a value to a variable no. of rows | Excel Discussion (Misc queries) | |||
Copying RANGE of variable length | Excel Programming | |||
Copying a variable range | Excel Programming | |||
count number of rows (variable range) | Excel Programming |