VBA Use Row Count to Copy and Insert same number of rows

[activeman]

Hi
I wonder if anyone can help – I have 2 separate spreadsheets within the same workbook.
Sheet1 contains several rows of basic data.

Sheets2 contains a single row of formulas which calculates based on the first row of data in Sheet1.
Using VB I would like to count the number of populated data rows in Sheet1.

Using this "count" I would then like to copy the formula row in sheet2, and then insert copied cells beneath the equivalent number times.

E.g. I have 30 rows of data in sheet1 – I would like the same number of formula rows displayed in Sheet2 from running Macro.
Any help would be much appreciated.

Phil

 

[machopicho]

Hi
I wonder if anyone can help – I have 2 separate spreadsheets within the same workbook.
Sheet1 contains several rows of basic data.

Sheets2 contains a single row of formulas which calculates based on the first row of data in Sheet1.
Using VB I would like to count the number of populated data rows in Sheet1.

On Column A:
nrows = Cells(Rows.Count,1).End(xlUp).Row

Using this "count" I would then like to copy the formula row in sheet2

Where?

...and then insert copied cells beneath the equivalent number times.

Of sheet 1?

E.g. I have 30 rows of data in sheet1 – I would like the same number of formula rows displayed in Sheet2 from running Macro.

Any help would be much appreciated.

Phil

 

[activeman]

Hi Machopicho<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Hopefully I've answered the questions in the right order:<o></o>
<o></o>
"Using this "count" I would then like to copy the formula row in sheet2"<o></o>
<o></o>
The formula row in sheet2 can be anywhere, but for arguments sake lets assume it is on row 15 in Sheet2.<o></o>
<o></o>
<o></o>
"...and then insert copied cells beneath the equivalent number times."<o></o>
<o></o>
In sheet2 beneath row 15 (as above)<o></o>
<o></o>
Basically I have one row of formulas in sheet2 and I am trying to avoid having to copy and paste the formula row down the spreadsheet a predetermined number rows in sheet2 that wait for the data to be pasted into sheet1 each time.<o></o>
<o></o>
The way I am trying to get round this is to create a macro that counts the rows of data pasted into sheet1 and applies the copy insert action in sheet2 the applicable number of times.<o></o>
<o></o>
Thanks Phil

 

[machopicho]

Try:

Sub test()
nrows = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Sheets(2).Rows("15").Copy
Sheets(2).Rows("16:" & nrows).Select
ActiveSheet.Paste
End Sub

 

[activeman]

You're a star - tweaked it slightly but works a dream

 

[Christiaan]

Hello everybody.

I have a similair kind of question... But actually it is the other way around.
I have a sheet with names, and a total number.

Now what I need, is a VBA-script that inserts the amount of rows, with that persons name.

So, for example:
Sheet 1 has the data:
Erick 25
Chris 90
Robert 54
Diego 12
Dennis 98

In sheet 2, I would need to get:
25 rows with Erick
90 rows with Chris
54 rows with Rober
12 rows with Diego and
98 rows with Dennis.

Is there anyway we can do this trough VBA?

 

[machopicho]

Hello everybody.

I have a similair kind of question... But actually it is the other way around.
I have a sheet with names, and a total number.

Now what I need, is a VBA-script that inserts the amount of rows, with that persons name.

So, for example:
Sheet 1 has the data:
Erick 25
Chris 90
Robert 54
Diego 12
Dennis 98

In sheet 2, I would need to get:
25 rows with Erick
90 rows with Chris
54 rows with Rober
12 rows with Diego and
98 rows with Dennis.

Is there anyway we can do this trough VBA?

Where has sheet 1 data?
Column A names and column B numbers?

 

[Davesh Garg]

Where has sheet 1 data?
Column A names and column B numbers?

Hi Machopicho,

Same issue I am having. Could you please help in providing the code regarding this?

Yes, Column A has names and Column B numbers.

Thanks!