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

[strat919]

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!

 

[Michael M]

@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....
Isn't Excel fun !!

 

[strat919]

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

 

[Michael M]

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 ??

 

[strat919]

I edited my last post instead of reply..... duh.....read above

 

[Michael M]

Ok try this...

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

 

[strat919]

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

 

[Michael M]

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 !

 

[strat919]

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

 

[strat919]

It worked perfectly! Thanks a million..... can I ask you one more question here, or should I post in another thread?

 

[Michael M]

You can ask here if you like.