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,974
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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,974
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,974
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,974
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,974
Office Version
2013
Platform
Windows
You can ask here if you like.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,764
Messages
5,488,715
Members
407,654
Latest member
IDAL

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top