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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
Hi Machopicho<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
Hello,
There are 24 columns data for the count is 7
That means on the basis of count copy the rows of data out of 24 column to 20 columns data ie 7 rows data please give the solution using VBA code
 
Upvote 0

Forum statistics

Threads
1,222,228
Messages
6,164,728
Members
451,912
Latest member
HMF009

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
Back
Top