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

activeman

New Member
Joined
Mar 1, 2011
Messages
15
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

machopicho

Well-known Member
Joined
Feb 28, 2010
Messages
606
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

New Member
Joined
Mar 1, 2011
Messages
15
Hi Machopicho<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Hopefully I've answered the questions in the right order:<o:p></o:p>
<o:p></o:p>
"Using this "count" I would then like to copy the formula row in sheet2"<o:p></o:p>
<o:p></o:p>
The formula row in sheet2 can be anywhere, but for arguments sake lets assume it is on row 15 in Sheet2.<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
"...and then insert copied cells beneath the equivalent number times."<o:p></o:p>
<o:p></o:p>
In sheet2 beneath row 15 (as above)<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Thanks Phil
 

machopicho

Well-known Member
Joined
Feb 28, 2010
Messages
606
Try:
Code:
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

New Member
Joined
Mar 1, 2011
Messages
15

ADVERTISEMENT

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

Christiaan

Board Regular
Joined
Nov 5, 2012
Messages
80
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

Well-known Member
Joined
Feb 28, 2010
Messages
606
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?
 

Forum statistics

Threads
1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top