Dynamic Pivot Table from Multiple Consolidation Ranges

[rasinc]

I have a pivot table that has been working for quite a while without any problems. Clients now wish to have the report for a dynamic and specific subset of data based on date range. In other pivot tables, I have been able to filter without problems but this one does not allow for date filtering.

The table is built from up to 93 different sheets using the same range on each sheet, same columns, etc. Each sheet is the data for one date. The data includes equipment/employee usage and time with different columns for rates like Standard Time, Overtime, Double Time, etc. The hours for each are totalled and the total dollars for each row are totalled.

Client originally had all sheets included for the entire project. Now they wish to provide their client with probably weekly totals (possibly just a couple of days). I just can't figure out how to narrow down the reporting.

The pivot table fields end up with Row, Column and Value. I have put them in the appropriate areas of Row labels, Column labels and Values (formatted as Sum). I cannot access any specific column so I cannot add the date to the columns. The left column of the pivot table is the description of the equipment or operator's name and other columns are sum of hours and total dollars.

I have recorded a macro to determine how to code the pivot table and when narrowed down to three dates, I get the following:

ActiveSheet.PivotTableWizard SourceType:=xlConsolidation, SourceData:=Array _
        ("'Dec 1, 2016'!R9C2:R48C14", "'Dec 6, 2016'!R9C2:R48C14", _
        "'Dec 8, 2016'!R9C2:R48C14")
With ActiveSheet.PivotTables("ptSummary").PivotFields("Row")
        .PivotItems("(blank)").Visible = False
End With

Once a user enters a date on a sheet, the sheet name gets that date with the following code in the SheetChange event.

strSheetName = Format(Sh.Range("F4").Value, "MMM d, yyyy")
Sh.Name = strSheetName

Because my client wishes to use a date range, I have set up a couple of fields for them to enter a start date and end date. I am trying to figure out how to turn those into sheet names for the code above. I tried to use a variable as the array so I could do something like

strArray = """'Dec 1, 2016'!R9C2:R48C14""" & ", " & """'Dec 6, 2016'!R9C2:R48C14""" & ", " & """'Dec 8, 2016'!R9C2:R48C14"""

ActiveSheet.PivotTableWizard SourceType:=xlConsolidation, SourceData:=Array _
        (strArray)

With ActiveSheet.PivotTables("ptSummary").PivotFields("Row")
        .PivotItems("(blank)").Visible = False
End With

but I get the error Cannot open PivotTable source file '".xls'Dec 1, 2016" on the ActiveSheet.PivotTableWizard line.

Ultimately I would just like to build strArray in code based on the date ranges selected and any sheet that matches a date would be added to the array, but I am stuck on this error based on just three sheets that do exist.

Can anyone point me in the correct direction or provide an alternative angle? Sorry my question ended up being so long.

TIA rasinc

 

[pbornemeier]

Are you running your code with the Pivot Table on the activesheet?

I would assume that one of the earlier SourceData statements in your code included a worksheet and/or workbook reference that the later ones did not need since you were working with the active pivot that had a range already defined. I use this code to update an existing PT:

Worksheets(sPivotTableSheet).PivotTables(lX).ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
sSourceSheet & "!" & ActiveWorkbook.Worksheets(sSourceSheet).Range("A1"). _
CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion10)

 

[rasinc]

Sorry for the long delay in replying Phil. Thank you for your post. I was pulled off this job, passed your message along and thought someone else had followed up. I will be reassigned next week back to this job, will see what the progress is and get back to you if they haven't sorted it out.

 

[rasinc]

Sorry Phil,

but I am just not able to manipulate your code to what I need. I keep getting errors.

First I assigned the variable sPivotTableSheet with the proper sheet name "EquipmentSummary" in my spreadsheet.

Then the .PivotTables(IX). I assumed referred to the ninth pivot table, I replaced IX with the "ptSummary" pivot table name in my file.

Now when I am trying to deal with the sSourceSheet variable I am a little stuck. My source data is on up to 93 sheets (see strArrray in my third code block on Post #1). Those names are variable based on the dates users put on the sheets. The range used R9C2:R48C14 is the same on all sheets and will not change. R9 is the column headers.

I tried replacing sSourceSheet with strArray but as suspected, it doesn't work and now I am getting the Subscript out of range error.

I'm starting to think I need to code this pivot table to a single sheet named range and to build the range, go through each sheet with the appropriate date range and copy the applicable data to that range whenever the users want the report for a specific date range. Do you think that might be easier in the long run rather than trying to get a pivot table to work off disconnected ranges on different sheets?

Thanks again for your help.

 

[SpillerBD]

An alternative angle would be using PowerQuery(2010-2013) or Get and Transform (2016) to consolidate the data.
That result may need to be loaded to Table for the end-user and the Pivot Table. Loading to a Data Model has advantages, but changes the availability of certain Pivot Table features that users may be familiar with.
The Power Query can add the sheet name as a data point and convert to date from the text value.

 

[rasinc]

Thanks SpillerBD. I only have Excel 2007 and I believe my client is running at least one computer with that version as well, so the 2010 and higher options might not work. I have decided to reprogram the report to pull data from just one sheet so I am doing the consolidation outside the pivot table and pointing the pivot table to just one dynamic named range on one sheet. It seems to be working a lot better and I can get better column headers as well.

Thanks to both of you for the options.