Copy rows down including all formats depending on a Cell number

[tyrese215]

Hi all,

I have a template that needs to be filled out by my client. Rather than pre-populating a worksheet with say 500 rows (which increases size of file), I want the user to enter how many records they need to fill in Cell A1, and then the code will copy the entire row in row 8, including all my formats, formulas down the page corresponding to the value in cell a1, just as if the user would copy each row down himself.

All rows that are copied down must have dynamic references, ie they need to be copied down.. just as if you were to use the fill handle when you put the cursor on the bottom right of a cell.


Secondly, I need some code that will automatically number cells A8, with 1, down to what ever number of records the user would like to generate which is the cell value in A1. So if the user enters 50 is cell A1, then cell A8, must say 1, A9 must say 2, A10 must say 3 all the way down to 50.

Please ensure that the formats and the cell colours used in row 8 are copied down as well corresponding to the value in cell 8..

Please help asap. I have this project due shortly..

Cheers and many thankyous....

 

[Brunts]

Not convinced that I understand your formatting requirements but try this for starters

Sub Expand_Template()
Dim Nf_Rows As Integer
Nf_Rows = Cells(1, 1).Value
Range(Cells(8, 2), Cells(8, 10)).AutoFill Destination:=Range(Cells(8, 2), Cells(Nf_Rows + 7, 10)), Type:=xlFillDefault
Cells(8, 1).Value = 1
Cells(9, 1).Value = 2
Range(Cells(8, 1), Cells(9, 1)).Select
Selection.AutoFill Destination:=Range(Cells(8, 1), Cells(Nf_Rows + 7, 1)), Type:=xlFillDefault
Range("A1").Select
End Sub