VBA code to copy X amount of rows from all sheets and paste to new sheet

strat919

Board Regular
Joined
May 15, 2019
Messages
54
I'm searching for VBA code that would copy X amount of rows starting with 1A from all sheets to a new sheet. Say if I had 13 sheets, it would copy the first 20 rows of every sheet and paste to a new sheet with the 260 rows. I would like to be able to specify and change the 20 to any amount.

I've searched pretty hard and have not seen one that will do this. This may be simple for someone, but not me:)

Any help much appreciated!
 
@My Aswer Is This
Hi Mait....Isn't it odd, I've been using MM1 for years, and it's only happened on about 5 occasions....:LOL:
Isn't Excel fun !!
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Ok... I got it:) I'm pretty new at this. I was putting the 20 inside of the quotes instead of deleting and placing the 20 after the ans=

Thank you very much:)

Is there any way for it to create the master sheet in the code without having to create it before I run the code? I will be doing this many times.

It works great and I guess beggers can't be choosy:)
 
Last edited:
Upvote 0
Ok, then you input how many rows you want to copy, click ok then F8...
As stated previously, have you changed the code in any way ??
 
Last edited:
Upvote 0
Ok try this...


Code:
Sub copyroze()
Dim lr As Long, ws As Worksheet, ans As Long
Sheets.Add(after:=Worksheets(Sheets.Count)).Name = "Master"
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
ans = InputBox("How many rows do you want copied ? ")
For Each ws In Worksheets
    If ws.Name <> "Master" Then
        With ws
        .Rows("1:" & ans).Copy Sheets("Master").Range("A" & lr)
        lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
        End With
    End If
Next ws
End Sub

What is this s'posed to mean ??

It works great and I guess beggers can't be choosy
 
Upvote 0
It just means don't ask for more than you already have....lol

I thought it worked, and for the most part it did..... but I get 260 rows as expected (20x13)... but 20 rows are blank except a 0 in the E column. I'm going to run everything again. I'm dealing with 13 million entries in 13 sheets. I'll try the code above

Thanks again:)
 
Upvote 0
I'm guessing you asked for 20 rows, but some sheets have some empty rows amongst that 20 requested..
AND
those rows probably contain formulas that are returning zero, because it can't find a result for said formula !
 
Upvote 0
There are no empty rows as far as I can see in any of the sheets.... I'm running entire process again and will report back in a few.....

Thanks so much for helping me with this:)
 
Upvote 0
It worked perfectly! Thanks a million..... can I ask you one more question here, or should I post in another thread?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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