Results 1 to 10 of 10

Thread: Collecting All Data from multiple tabs in excel 2010
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2014
    Posts
    271
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Collecting All Data from multiple tabs in excel 2010

    Hi - is there a very quick way to collect all data stored in the same spreadsheet but on multiple tabs and copy them into a new tab please.

    All headings are the same in each

    Thanks

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,857
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Collecting All Data from multiple tabs in excel 2010

    Create a sheet and name it "Summary". Place all the headings in row 1. Place this macro in a standard module and run it from there.
    Code:
    Sub CopySheetData()
        Application.ScreenUpdating = False
        Dim ws As Worksheet, desWS As Worksheet
        Set desWS = Sheets("Summary")
        For Each ws In Sheets
            If ws.Name <> "Summary" Then
                ws.UsedRange.Offset(1, 0).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next ws
        Application.ScreenUpdating = True
    End Sub
    Last edited by mumps; May 3rd, 2019 at 10:52 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Feb 2014
    Posts
    271
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Collecting All Data from multiple tabs in excel 2010

    Thanks but just getting error messages. Compile Error - Expected End Sub

    I have created a new tab called "Summary" as advised - just in case all of my other tabs have different names though

    What is a standard module please
    Last edited by philb99; May 3rd, 2019 at 11:29 AM.

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,857
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Collecting All Data from multiple tabs in excel 2010

    Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. This will be your standard module. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. The error you received seems to indicate that the last line of the macro (End Sub) is missing. Please make sure that line is not missing.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Board Regular
    Join Date
    Feb 2014
    Posts
    271
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Collecting All Data from multiple tabs in excel 2010

    Thanks again - I am now getting Run time error 9 - subscript out of range in the grey box

    its highlighting the following part of the code with a yellow background

    Set desWS = Sheets("Summary")
    Last edited by philb99; May 3rd, 2019 at 05:00 PM.

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,857
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Collecting All Data from multiple tabs in excel 2010

    Do you mean a sheet with that name?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  7. #7
    Board Regular
    Join Date
    Feb 2014
    Posts
    271
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Collecting All Data from multiple tabs in excel 2010

    In the macro its your code that is being highlighted my sheet has been set up as "Summary"

    Thanks

  8. #8
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,857
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Collecting All Data from multiple tabs in excel 2010

    I think that it would be easier to help and test possible solutions if I could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  9. #9
    Board Regular
    Join Date
    Feb 2014
    Posts
    271
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Collecting All Data from multiple tabs in excel 2010

    Hi - I have now just taken out the "" at either end of Summary and all works perfectly - thanks for your help and support. This is just what I wanted - Brilliant

  10. #10
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,857
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Collecting All Data from multiple tabs in excel 2010

    My pleasure.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

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
  •