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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,967
Office Version
2013
Platform
Windows
@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 !!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,967
Office Version
2013
Platform
Windows
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:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,967
Office Version
2013
Platform
Windows
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
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,967
Office Version
2013
Platform
Windows
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

Board Regular
Joined
May 15, 2019
Messages
54
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

Board Regular
Joined
May 15, 2019
Messages
54
It worked perfectly! Thanks a million..... can I ask you one more question here, or should I post in another thread?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,967
Office Version
2013
Platform
Windows
You can ask here if you like.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,378
Messages
5,486,517
Members
407,550
Latest member
LucasBordure

This Week's Hot Topics

Top