Power Pivot - How to

You can make a pivot table using consolidation ranges but they are quite limited in terms of what you can do with them. Power Pivot won't help, but Power Query will if you have it as it can merge all the tables in to one data source.
You could also use code to either consolidate the data into one tab, or to create a recordset from all of them that you can then assign to your pivot table.

You may be relieved to know that you can use the standard PivotTables for this.
 
Have a look at this article: https://support.office.com/en-au/article/Consolidate-multiple-worksheets-into-one-PivotTable-report-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5

Just note that they are extremely limited. The only use I have ever had for consolidation range pivot tables is actually to normalise data.

Thank you for the info.  I went to the link.  What I am concerned about is once I set a named range on each of these sheets, I would want the range. To dynamically adjust if the data range has expanded (ie more rows).  

If the sheet was in the format of a table, and I assigned a named range to that table, would the names range adjust if the number of rows increased?

Thank you

Philip,

I did the PIvot as suggested but the results are not like I expected.  YOu can only choose Row, Column, Value.  The results are only a count of items and this is not what I am looking for.   I do not need a count for any of the various column heading.  I need the actual text (ie the person's name and not a count of that name and etc etc for the entire Pivot TAble.  I think this solution is only if you are interested in summing and I am not interested in this.  

If you have another idea let me know.  

Rory, not sure if you are folliwing me but does your solution shows the actual text and not counting.  

thank you

What about combining all of the data onto one sheet before you start?

that is what I am trying to avoid to do this manually.

to combine the data manually on one sheet.  There are many sheets and it is quite tedious and time consuming and opens door to make a lot of mistakes.

One quick question...is this data which you have in multiple sheet tabs is it unique or duplicate because what i will say if its a unique data and you can have 1 to 1 relationship then convert that range to table and create a power pivot over it since in the power pivot you can do this feature easily by doing 1 to 1 relationship..

But please note if the data is not 1 to 1 then in the power pivot you won't be able to create a relationship as a result you won't be able to show them in powerpivot as well..

Alternatively if you are open a vba code can be written easily for you which can combine all of this data automatically on 1 sheet and on top of which you can make your pivot table..what i mean is something like this..this will combine all your data into sheets called combine... from each worksheet...this i have wrote for column A to D you can accordingly adjust the range basis to your needs...

Sub consolidate()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet, lrow As Long, lr As Long
    On Error Resume Next
    Sheets("Combine").Delete
    On Error GoTo 0

    For Each ws In ActiveWorkbook.Worksheets

        Set ws1 = Sheets("Combine")

        If ws1 Is Nothing Then
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = "Combine"
            Set ws1 = ActiveSheet
            ws.Range("A1:D1").Copy ws1.Range("A1")    '<---Copying headers
        End If
        lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row    '<--Finding last row basis of A Column
        lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1    '<--Determining past range
        ws.Range("A2:D" & lrow).Copy ws1.Range("A" & lr)

    Next ws
End Sub

Select allOpen in new window

Saurabh...

Hi Saurabh, thank you for your response.  Quick question:  I have many tabs but some f the tabs I would not need to combine the data which means I basically would it combine ALL sheets.  Is it possible to only combine the sheets with a common denominator such as the word "mod" in the name of the sheet?

Thank you much

In that case you can use something like this...

Sub consolidate()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet, lrow As Long, lr As Long
    On Error Resume Next
    Sheets("Combine").Delete
    On Error GoTo 0

    For Each ws In ActiveWorkbook.Worksheets

        If InStr(1, ws.Name, "mod", vbTextCompare) > 0 Then

            Set ws1 = Sheets("Combine")

            If ws1 Is Nothing Then
                Sheets.Add after:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Combine"
                Set ws1 = ActiveSheet
                ws.Range("A1:D1").Copy ws1.Range("A1")    '<---Copying headers
            End If
            lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row    '<--Finding last row basis of A Column
            lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1    '<--Determining past range
            ws.Range("A2:D" & lrow).Copy ws1.Range("A" & lr)
        End If

    Next ws
End Sub

Select allOpen in new window

This will combine all the worksheets in combine sheet which has mod in it..

Saurabh...

Do you not have Power Query?

Saurabh, thank you very much.  I will test soon.
Rory:  I do not have Power Query.  I do have Power Pivot.  I have very limited experience with PP and have no experience with PQ.  I could install then learn how to use it.  From what I gather PQ could be a solution but I do not know how much I would need to do in order to feel comfortable using it.

If you can use PQ, and format the source data as Tables, you can then have a 'live' connection to the data and all you would need to do is refresh the pivot table when the source data changes.

I will have to explore the VBA code method to combine sheets from Saurabh as I cant install on this computer as it locked down.  unfortuantely.  

Saurabh:  I get an error on line
Set ws1 = Sheets("Combine")

I added a sheet "Combine" and the code does delete the sheet but possibly the sheet "Combine" needs to be recreated and this is why I get the error as it cant find the sheet?  

let me know.  Waiting for your response.

In my code you don't have to do anything it will create the sheets combine on its own..all you need to change the range of the code and that will copy from all the worksheets which have mod in it in the sheets combine...

Oh I see.   I deleted the sheet "combine" but I still get that error on that line.  

I have uploaded the test file for your review.  
The code is in the excel file.  

when you have a sec, please let me know where I have made a mistake.
Combine-Sheets-with-VBA.xlsm

Wow!  that works perfectly.  Thank you so much for your help.

Saurabh,

So sorry to pester you.   I hope you are still following this thread.

I have encountered something unknown.
I transferred to code to my workbook to do the real testing and I get a runtime error 9 "subscript out of range".

I have attached the spreadsheet.
Possibly you see an issue?
I have many more sheets than the attached spreadsheet but I have deleted all sheets but one and the remaining sheet does have the word "mod" in the sheet name as required.  I still get the error with one sheet.    
The range was modified out to column P and possibly that is the reason as I might not have edited the code properly.  

Please let me know when you have a second.  

thank you
TEST-Combine--sheets-deleted-.xlsm

You are still using an old version of the code..Use this version and this will do what you are looking for...

Sub consolidate()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet, lrow As Long, lr As Long
    On Error Resume Next
    Sheets("Combine").Delete

    For Each ws In ActiveWorkbook.Worksheets

        If InStr(1, ws.Name, "mod", vbTextCompare) > 0 Then
            On Error Resume Next
            Set ws1 = Sheets("Combine")

            If ws1 Is Nothing Then
                Sheets.Add AFTER:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Combine"
                Set ws1 = ActiveSheet
                ws.Range("A1:P1").Copy ws1.Range("A1")    '<---Copying headers
            End If
            lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row    '<--Finding last row basis of A Column
            lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1    '<--Determining past range
            ws.Range("A2:P" & lrow).Copy ws1.Range("A" & lr)


        End If

    Next ws
    ws1.Cells.EntireColumn.AutoFit

End Sub

Select allOpen in new window

Saurabh...

Thank you once again.  It works now.  Appreciate your help dearly...