VBA code to copy X amount of rows from all sheets and paste to new sheet
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 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. #11
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,538
    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

    @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 !!
    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]

  2. #12
    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

    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 by strat919; Jul 18th, 2019 at 10:46 PM.

  3. #13
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,538
    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

    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 by Michael M; Jul 18th, 2019 at 10:45 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]

  4. #14
    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 edited my last post instead of reply..... duh.....read above

  5. #15
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,538
    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

    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
    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. #16
    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

    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

  7. #17
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,538
    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'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 !
    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. #18
    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

    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

  9. #19
    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

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

  10. #20
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,538
    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

    You can ask here if you like.
    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
  •