Forum Discussion

ExcelExciting's avatar
ExcelExciting
MVP
Dec 01, 2020

Hi PeterW1 

 

There are different ways to handle it, I will be sharing you two solution & see what suites best for you

 

Solution 01

You can use this VBA code to combine all the data into your workbook.

Combine Active Workbook All Sheets into One Master Sheet with VBA | Excel Exciting

 

Note: looking the structure of your data note that code will break as the column A is empty. Start from the from A1

 

Solution 02 - [Excel 2010 & Above] 

This dynamic solution 

1. Convert your data into Excel Table (ctrl + t)

2. Give you table name 

3. Go to Data > GET Data > From Other Source > Blank Query

4. Power Query Editor

5. Incase if your formula bar is not enabling then click on VIEW > Layout > check the formula bar

6. In the formula bar enter "= Excel.CurrentWorkbook()" 

Note that this case sensitive

7. Select the table you like to combine

8. click on content expand table 

 

You will have all your records at one place. this is recommended solution

 

Note: you make your table header same before combining the records for both the solutions

 

Attached is the sample file.

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

  • PeterW1's avatar
    PeterW1
    Copper Contributor
    Dec 02, 2020

    Hi ExcelExciting 

     

    Thank you very much for replying.

     

    I tried your Solution 02.

    But the only options I have for getting data from other source is From HTML, From Text, New Database Query [SQL Server ODBC, From Database]. there is no option for Blank Query.

     

    I should probably have mentioned that I work in Excel 365 for Mac.

     

    Peter

    • ExcelExciting's avatar
      ExcelExciting
      MVP
      Dec 02, 2020

      PeterW1 

       

      Then you need go with the solution number 01, as rightly said by SergeiBaklan currently Power Query for mac is limited. 

       

      Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

      If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

       

      • Nena99's avatar
        Nena99
        Copper Contributor
        Apr 08, 2022

        ExcelExcitingI have a problem of combining two tables from two different workbooks.

         

        I managed to solvethe first problem which is bringing the data based on the word "active"

        Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
    ' loop column H untill last cell with value (not entire column)
    For Each Cell In .Range("G1:G" & .Cells(.Rows.Count, "H").End(xlUp).Row)
        If Cell.Value = "Active" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .Rows(Cell.Row).Copy Destination:=Sheets(4).Rows(Cell.Row)
            
        End If
    Next Cell End With

End Sub

        However, I have three issues.
        1)There is empty rows in between that was not active I want to delete them.
        2)Based on the ID number that is associated with active word, the macro needs to find the ID in the second workbook and copy the row to the new table.
        3)There is empty cells, or errors like#N/A, the code needs to return back to me.
        Any suggestions how to fix my code in VBA?

        I appreciate your help 

        Best Regards

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 02, 2020

      PeterW1 

      Yes, Power Query on Mac is quite limited. 

Resources