Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: VBA code to copy X amount of rows from all sheets and paste to new sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    I failed to mention that your destination sheet in this case needs to be called "Master"
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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 by Michael M; Jul 18th, 2019 at 10:17 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,822
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

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

    I was one. I had trouble running scripts with the name MM1. Running Excel 2013. I just changed the script name.
    Quote Originally Posted by Michael M View Post
    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

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

    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
    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •