Power Pivot - How to

Hello,

I have data on many sheets.  
All the column headings are the same.
I want to make a pivot of this data on many sheets.  

Is this something for power pivot or is there another solution?
pdvsaProject financeAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
Their you go i created some more sample data in your worksheet with 10 headers which 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:J1").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:J" & lrow).Copy ws1.Range("A" & lr)
        End If

    Next ws
End Sub

Open in new window


Your workbook...

Saurabh...
Combine-Sheets-with-VBA.xlsm
0
 
Rory ArchibaldCommented:
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.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Rory ArchibaldCommented:
Just note that they are extremely limited. The only use I have ever had for consolidation range pivot tables is actually to normalise data.
0
 
pdvsaProject financeAuthor Commented:
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
0
 
pdvsaProject financeAuthor Commented:
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
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What about combining all of the data onto one sheet before you start?
0
 
pdvsaProject financeAuthor Commented:
that is what I am trying to avoid to do this manually.
0
 
pdvsaProject financeAuthor Commented:
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.
0
 
Saurabh Singh TeotiaCommented:
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

Open in new window


Saurabh...
0
 
pdvsaProject financeAuthor Commented:
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
0
 
Saurabh Singh TeotiaCommented:
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

Open in new window


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

Saurabh...
0
 
Rory ArchibaldCommented:
Do you not have Power Query?
0
 
pdvsaProject financeAuthor Commented:
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.
0
 
Rory ArchibaldCommented:
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.
0
 
pdvsaProject financeAuthor Commented:
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.
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
pdvsaProject financeAuthor Commented:
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
0
 
pdvsaProject financeAuthor Commented:
Wow!  that works perfectly.  Thank you so much for your help.
0
 
pdvsaProject financeAuthor Commented:
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
0
 
Saurabh Singh TeotiaCommented:
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

Open in new window


Saurabh...
0
 
pdvsaProject financeAuthor Commented:
Thank you once again.  It works now.  Appreciate your help dearly...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.