dcsimg
Results 1 to 4 of 4

Thread: How do I combine data from multiple tabs in excel into one?

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    8

    Post How do I combine data from multiple tabs in excel into one?

    I have a large excel workbook that has data from multiple queries from an access database.

    Depending on a client's activity, each tab could either have data or have no data at all. There are roughly 30 tabs in my workbook that either have data (# of rows of data can vary) or no data.

    I need to summarize all of the client's activity on one summary sheet in the same workbook.

    How do I transfer varying # of lines that have data from each tab in the workbook to this one summary page.

    Currently I'm going into each tab and manually copying data myself and pasting it into the summary sheet, but don't think this is feasible in the long run.

    Is there a way for excel to automatically check each specified tab for lines with data (# of rows could vary) on each tab of the workbook and lay it out all one summary tab knowing where to place it so it doesn't overlap another tab's data?

    Thank you for your kind attention.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: How do I combine data from multiple tabs in excel into one?

    do the sheets have header rows or are the completely empty if no data?

    you can count the number of cells with data in each sheet, if the number of ells with data is > 0 or the number of cells within any header then copy the data below previous data in summary sheet, all fairly straight forward, i believe you could use the worksheetfunction counta to do this
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    8

    Re: How do I combine data from multiple tabs in excel into one?

    Quote Originally Posted by westconn1 View Post
    do the sheets have header rows or are the completely empty if no data?

    you can count the number of cells with data in each sheet, if the number of ells with data is > 0 or the number of cells within any header then copy the data below previous data in summary sheet, all fairly straight forward, i believe you could use the worksheetfunction counta to do this

    Hi Westconn1,

    Thanks for your feedback. Could you please give example of some code for this scenarios that I can follow? I'm fairly new to VBA so have been using a lot of code examples online to figure out how to get it in the format I need it to be in.

    There are headers on each page regardless of whether or not there is data.

    Appreciate your help.

    Thanks,
    Nisha

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,886

    Re: How do I combine data from multiple tabs in excel into one?

    you can test this to see if it works how you want
    Code:
    Dim sht As Worksheet, sumsht As Worksheet, cols As Integer
    cols = 10    '  change to number of columns in data
    Set sumsht = ThisWorkbook.Sheets("Summary Sheet")   ' change to suit
    For Each sht In ThisWorkbook.Sheets
        If Not sht.Name = sumsht.Name Then
        With sht.Range("a2")
            If Not IsEmpty(.Value) Then
                .Resize(.Offset(Rows.Count - .Row).End(xlUp).Row - 1, cols).Copy
                sumsht.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial
            End If
        End With
        End If
    Next
    Application.CutCopyMode = False
    this assumes that if there is data in each worksheet then A2 is not empty
    it copies the data consecutively to the summary sheet without gaps or header rows
    if the code is run again it will just append the data again
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width