VBA to paste based on cell contents

[raph87]

I am creating a sheet that will provide a number of rows based on how many rows the user has. I have a row as a template.

Example

#	Server ID	Memory	CPU	Location	Brand
1	123	12	2	dallas	HP

<tbody>
</tbody>

Upon entering the sheet you have 1 row to input your information. But if you have more than 1 server you want to input I want to copy the first row based on that number. So if you say 5 servers then I want row 1 to paste 5 times under row 1. Row 1 will always be blank upon entry. Basically I want to have a marco create a table with the number of rows inputted by the customer. So if they say 5, I paste 5 rows. Hopefully that makes sense and is easy.

I am having trouble coding the input (5 in this example) to become a string in which I can paste based upon

Thanks in advance

 

[JoeMo]

If the first data row is blank when the user accesses the sheet, does the user enter all the data in the first data row and then you want the next 4 rows (if user has 5 servers) to populate identically to the first row?

 

[raph87]

If the first data row is blank when the user accesses the sheet, does the user enter all the data in the first data row and then you want the next 4 rows (if user has 5 servers) to populate identically to the first row?

The first row will be a template. So the inputs are drop downs like 1-5, yes no, HP IBM, etc. So based on how many data rows(servers) they have, I want to copy the first row.

At the top of the sheet I will have a cell that reads: "Number of servers" { }. What ever they put in there I want the table to reflect the number of rows. The first row: Server, Memory, CPU, Brand, etc will always be there. The rows below it will be my template rows. I want to paste the template for as many times servers they indicated.

Thanks for ur help Joe. I think you helped me last time and it was perfect

 

[JoeMo]

The first row will be a template. So the inputs are drop downs like 1-5, yes no, HP IBM, etc. So based on how many data rows(servers) they have, I want to copy the first row.

At the top of the sheet I will have a cell that reads: "Number of servers" { }. What ever they put in there I want the table to reflect the number of rows. The first row: Server, Memory, CPU, Brand, etc will always be there. The rows below it will be my template rows. I want to paste the template for as many times servers they indicated.

Thanks for ur help Joe. I think you helped me last time and it was perfect

This assumes the first header is in cell A1- adjust to suit. Also assumes the drop downs are validation lists, not list boxes or combo boxes. It prompts the user for the number of servers. If you would rather have the user enter that number in a cell, tell me which cell that will be.

Sub ExtendTemplate()
Dim N As Long, R As Range
N = InputBox("Enter the number of servers")
Set R = Range("A1").CurrentRegion.Offset(1, 0)
R.Resize(N, R.Columns.Count).FillDown
End Sub

 

[raph87]

This assumes the first header is in cell A1- adjust to suit. Also assumes the drop downs are validation lists, not list boxes or combo boxes. It prompts the user for the number of servers. If you would rather have the user enter that number in a cell, tell me which cell that will be.

Sub ExtendTemplate()
Dim N As Long, R As Range
N = InputBox("Enter the number of servers")
Set R = Range("A1").CurrentRegion.Offset(1, 0)
R.Resize(N, R.Columns.Count).FillDown
End Sub

This works fine, but how do I adjust where the row template is. The row starts on B5

 

[JoeMo]

This works fine, but how do I adjust where the row template is. The row starts on B5

If the header row begins with B4 (#) then change "A1" to "B4" in the 4th line.

 

[raph87]

If the header row begins with B4 (#) then change "A1" to "B4" in the 4th line.

Thanks for all your help! Works great!

 

[JoeMo]

Thanks for all your help! Works great!

You are welcome.