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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,952
Office Version
2013
Platform
Windows
Maybe this then...


Code:
Sub MM1()
Dim lr As Long, ws As Worksheet, ans As Long
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,952
Office Version
2013
Platform
Windows
I failed to mention that your destination sheet in this case needs to be called "Master"
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
I created a sheet called Master. I changed the
("How many rows do you want copied ? ") to ("20")

When I run the macro MM1 macro, the only option I get is create....not run. I must be doing something wrong?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,952
Office Version
2013
Platform
Windows
Where did you paste the code ?
Put it into the "This Workbook" module.
To run it press ALT + F8 and select MM1 from the list and press run
OR
you can assign it to a shape or button on the Master sheet if you want to.
 

strat919

Board Regular
Joined
May 15, 2019
Messages
54
I created module under VBAProject(Distance Workbook.xlsm) which is my workbook name. I entered the code and changed as above.
It shows up in the Macro as MM1, but only create option is offered.

I have created quite a few modules, but have never seen only the create option.

By the way, how do you ad the code box in these threads?

Thanks
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,952
Office Version
2013
Platform
Windows
If you click anywhere in the code itself then press F8, does it start at the first line...usually in yellow. continue with F8 to step through the code?
Also, try changing the name of the macro to something else, I have had rare instances of people having issues with MM1...as a cell reference instead of a macro.
 
Last edited:

strat919

Board Regular
Joined
May 15, 2019
Messages
54
I just changed the name to CombineRowsToSheet and now get the run, but when I click on run, I get a dialog box to enter something in. The dialog box is named Microsoft Excel.... it has a 20 in it, and blank field....and an OK and Cancel buttons.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
I was one. I had trouble running scripts with the name MM1. Running Excel 2013. I just changed the script name.
If you click anywhere in the code itself then press F8, does it start at the first line...usually in yellow. continue with F8 to step through the code?
Also, try changing the name of the macro to something else, I have had rare instances of people having issues with MM1...as a cell reference instead of a macro.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,952
Office Version
2013
Platform
Windows
Have you modified the code I provided in any way ??
The line in red asks you to input a number defining how many rows on each sheet you want copied.....as per your request to be able to change the row to any amount !
If you simply enter 20 into the blank "box" and press OK, it will copy 20 rows ffrom each sheet
If you enter 5 into the blank "box" and press OK, it will copy 5 rows ffrom each sheet

Code:
Sub MM1()
Dim lr As Long, ws As Worksheet, ans As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
[color=red]ans = InputBox("How many rows do you want copied ? ")[/color]
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,960
Messages
5,483,898
Members
407,420
Latest member
Vinu78

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top