Combine Worksheets that are NOT tables

[sscornav]

I have multiple worksheets that are NOT tables and I don't want them to become tables after I do PowerQuery.

I would like to combine worksheets A, B, C which are not tables, and combine and transform the data into a table on a worksheet called summary.

Everything I see is in regard to combining tables, but that is not possible here. Thanks in advance for any help!

Steve

 

[Herakles]

Give this a go. I'd made it flexible so that you can copy dat from any number of worksheets
to another worksheet and create a table. You just need to change the worksheet names and the table name.

It assumes that all worksheets have the same number of columns and that they are in the same order on each sheet.

Public Sub subCombineWorksheets()
Dim strWorksheets As String
Dim arr() As String
Dim i As Integer
Dim rngData As Range
Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim strMsg As String

On Error GoTo Err_Handler
    
    Worksheets("Summary").Cells.Clear

    strWorksheets = "A,B,C"
    
    arr = Split(strWorksheets, ",")
    
    lngLastColumn = Worksheets(arr(0)).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
    Worksheets(arr(0)).Range("A1").Resize(1, lngLastColumn).Copy Destination:=Worksheets("Summary").Range("A1")
    
    For i = LBound(arr) To UBound(arr)
        
        lngLastRow = Worksheets(arr(i)).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        lngLastColumn = Worksheets(arr(i)).Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        
        Set rngData = Worksheets(arr(i)).Cells(2, 1).Resize(lngLastRow - 1, lngLastColumn)
        
        lngLastRow = Worksheets("Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        rngData.Copy Destination:=Worksheets("Summary").Range("A" & lngLastRow + 1)
                    
        strMsg = strMsg & arr(i) & vbCrLf
        
    Next i
    
    With Worksheets("Summary")
        .ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "SummaryTable"
        .ListObjects(1).HeaderRowRange.Font.Color = vbBlack
        .Range("A1").CurrentRegion.EntireColumn.AutoFit
    End With
        
    strMsg = "The following worksheets have been copied to the Summary worksheet." & vbCrLf & vbCrLf & strMsg
    
    MsgBox strMsg, vbInformation, "Confirmation"

Exit_Handler:

    Exit Sub

Err_Handler:

    strMsg = "There has been an error." & vbCrLf & vbCrLf & "Error Number : " & Err.Number & vbCrLf & "Description : " & Err.Description
    
    MsgBox strMsg, vbInformation, "Error"
    
    Resume Exit_Handler
    
End Sub

 

[sscornav]

Thanks, I will give it a try.

Are there any tricky ways to use Power Query as opposed to VBA??

 

[Herakles]

I've not used Power Query yet but what do you mean by 'tricky ways'?

 

[sscornav]

If all of my worksheets were tables, I could easily combine them with Power Query in a few steps with no code. However, I can't make them tables, so what I am looking for is an easy way to use PQ to combine worksheets WITHOUT turning them into tables.

Thanks!

 

[Herakles]

Use VBA to copy the data in these worksheets to other temporary worksheets and concert the data range to tables, use PQ for the
combine process and then delete the temporary worksheets.

I don't know PQ so I take your word for it that it can only work on tables.

 

[jdellasala]

VBA is not as agile as Power Query which is designed to solve this specific task. I suggest you view this video which was just posted four days ago. It is very long, but has a Table of Contents that allows you to skip to the relevant section (I suggest section 11. (25:43) Import Excel File, and section 12. (26:31) Excel.Workbook function.
If you need to do this in your Summary Workbook on a regular basis, Power Query is the way to go. As long as the source file names don't change, simply open the Summary Workbook and click Refresh. If the names change or more than one file is needed, it's easy to pull in more than one file in a folder (there are sections for that too).
If you need to do this more than once on different files, you might want to look at this post.

 

[sscornav]

Thanks, but my question is about combining data in a single workbook that is not stored in tables. Also, I don't want to turn that data into tables.

I have used "Print Area" as a virtual table to refer to in PQ, but it seems kind of clunky. There must be a better way.

 

[jaeiow]

Power query can take a dynamic array stored in a named formula. If that is what you mean by "Print Area", then that is it, and it can be dynamically wrapped around a data frame, using Filter, or whatever. What version of Excel are you on? I just switched over a workbook to using dynamic named ranges as a source to Power Query, and it doesn't seem clunky at all, to me. My personal "virtual table" formula looks like this:

=Sheet1!$D$4:INDEX(Sheet1!$H$4:Sheet1!$H$5001,ROWS(Sheet1!$E$4#))

. This might look obtuse but it uses a dynamic array and user input as a source.