Consulting

Results 1 to 3 of 3

Thread: Combine several sheets - Union, ADO, SQL, Consolidate, Formula?

  1. #1
    VBAX Tutor
    Joined
    Mar 2010
    Posts
    287
    Location

    Post Combine several sheets - Union, ADO, SQL, Consolidate, Formula?

    I have been working on a method of consolidating customer data. This data is used to create a summary table. Rather than have one 'live' table, I use source data to create a table, so that it remains flexible (and individual sheets can be exported to other workbooks). The summary table is then re-produced.

    I am hoping to automate this process so that I can run a macro that will loop through all sheets and produce a summary table. The desired output would work as follows:

    Worksheets (x10)
    Heading in column A
    Data in column B
    These worksheets are all name 'Data_Sheet1' 'Data_Sheet2' etc (Data_ is the convention)

    Summary
    Heading along Row 1
    Data consolidated below headings

    I am not sure what is the best process for consolidating this data - union, ADO, SQL, consolidate, or a table made of formulas produced using a macro?

    An example:

    Data_Sheet1

    Name...David
    Age....24
    Town...Reading

    Data_Sheet2
    Name...Peter
    Age....30
    Town...Cambrian

    Data_Sheet3
    Name...Michael
    Age....40
    Town...Brecon


    Summary
    Name......Age....Town
    David.....24.....Reading
    Peter.....30.....Cambrian
    Michael...40.....Brecon


    How can this be acheived? It will likely use arrays and loops to form a table, or an SQL/ADO style approach. Struggling with the code as very rusty.

    Windows 7
    Excel 2003
    Excel 2007
    Last edited by theta; 10-20-2015 at 11:39 PM.

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    below macro shoud work; provided that
    - worksheet names start with "Data_"
    - headers are in A1:A3
    - data cells are B1:B3

    if not, you should amend the code.

    Sub vbax_54050_consolidate_sheets()
    
        Dim ws As Worksheet
        Dim i As Long
        
        With Worksheets("Summary")
            .Cells.Clear
            .Range("A1:C1").Value = Array("Name", "Age", "Town")
            i = 2
            For Each ws In Worksheets
                If Left(ws.Name, 5) = "Data_" Then
                    .Range("A" & i & ":C" & i).Value = Application.Transpose(ws.Range("B1:B3").Value)
                    i = i + 1
                End If
            Next ws
        End With
    
    End Sub
    worksheet names start with "Data_"
    headers in A1:A3
    data in B1:B3
    Last edited by mancubus; 10-22-2015 at 12:07 PM. Reason: typo
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      with createobject("scripting.dictionary")
        .item(.count)=array("name","age","town")
    
        for each sh in sheets
           if sh.name<>"summary" then .item(.count)=application.transpose(sh.cells(1,2).resize(3))
        next
    
        sheets("summary").cells(1).resize(.count,3)=application.index(.items,0,0)
      end with
    End Sub

Posting Permissions

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