Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling Forward Pivot Table Source Data
Hello,
I am using a pivot table to consolidate the financial statements of 26 facilities. How do I roll forward the pivot tables to point at the next month's facility files without having to recreate a new pivot table each month? Thanks -- Thank You Andrew Edmunds |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling Forward Pivot Table Source Data
I'm guessing you could define a dynamic named range for the Pivot Source.
It's far easier to give a suggestion when you know how the source data is set up. -- HTH, Barb Reinhardt "AndrewEdmunds" wrote: Hello, I am using a pivot table to consolidate the financial statements of 26 facilities. How do I roll forward the pivot tables to point at the next month's facility files without having to recreate a new pivot table each month? Thanks -- Thank You Andrew Edmunds |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling Forward Pivot Table Source Data
Hello and thanks for your reply,
I'll try to explain what I mean as it is complicated. I have the financial statements for each of the 26 facilities for September. In a separate workbook, I created a pivot table to consolidate the 26 facilities. For October, I copy all of the facility files from September into an October folder and update the files with October's information. If I copy the pivot table file into the October folder the source data still points to the September files. How can I get it to point to October without having to go through and resetup each link? Thanks, -- Thank You Andrew Edmunds "Barb Reinhardt" wrote: I'm guessing you could define a dynamic named range for the Pivot Source. It's far easier to give a suggestion when you know how the source data is set up. -- HTH, Barb Reinhardt "AndrewEdmunds" wrote: Hello, I am using a pivot table to consolidate the financial statements of 26 facilities. How do I roll forward the pivot tables to point at the next month's facility files without having to recreate a new pivot table each month? Thanks -- Thank You Andrew Edmunds |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling Forward Pivot Table Source Data
I'm going to assume you're talking about changing the pivot source for all
pivot tables within a given workbook. If this is it, try this. It just replaces the old file name with the new file name. If you want to change the path, you can do that as well. Option Explicit Sub ChangePivotSource() Dim WS As Worksheet Dim aWB As Workbook Dim myPivot As PivotTable Dim OldFile As String Dim NewFile As String Dim myString As String OldFile = "myFile.xls" NewFile = "myNewFile.xls" Set aWB = ActiveWorkbook For Each WS In aWB.Worksheets For Each myPivot In WS.PivotTables Debug.Print myPivot.Name, myPivot.SourceData myString = Replace(myPivot.SourceData, OldFile, NewFile) myPivot.SourceData = myString Next myPivot Next WS End Sub -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "AndrewEdmunds" wrote: Hello and thanks for your reply, I'll try to explain what I mean as it is complicated. I have the financial statements for each of the 26 facilities for September. In a separate workbook, I created a pivot table to consolidate the 26 facilities. For October, I copy all of the facility files from September into an October folder and update the files with October's information. If I copy the pivot table file into the October folder the source data still points to the September files. How can I get it to point to October without having to go through and resetup each link? Thanks, -- Thank You Andrew Edmunds "Barb Reinhardt" wrote: I'm guessing you could define a dynamic named range for the Pivot Source. It's far easier to give a suggestion when you know how the source data is set up. -- HTH, Barb Reinhardt "AndrewEdmunds" wrote: Hello, I am using a pivot table to consolidate the financial statements of 26 facilities. How do I roll forward the pivot tables to point at the next month's facility files without having to recreate a new pivot table each month? Thanks -- Thank You Andrew Edmunds |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling Forward Pivot Table Source Data
Thank you very much for your response.
Two things, the first is that it is a pivot table from multiple workbooks so there are 26 paths that I need to change for each pivot table. Would that change the coding? Second, I do not know visual basic very well, is there anyway to do this without visual basic. Before I started using pivot tables, the spreadsheets I used were linked by cell reference. When I copied and pasted the source workbooks and the linked workbooks at the same time into a new folder the links automatically updated to the new path. Is there any way for the pivot table links to automatically update as well? I know I'm probably grasping at straws here but I do really appreciate all of your help. Have a great day! -- Thank You Andrew Edmunds "Barb Reinhardt" wrote: I'm going to assume you're talking about changing the pivot source for all pivot tables within a given workbook. If this is it, try this. It just replaces the old file name with the new file name. If you want to change the path, you can do that as well. Option Explicit Sub ChangePivotSource() Dim WS As Worksheet Dim aWB As Workbook Dim myPivot As PivotTable Dim OldFile As String Dim NewFile As String Dim myString As String OldFile = "myFile.xls" NewFile = "myNewFile.xls" Set aWB = ActiveWorkbook For Each WS In aWB.Worksheets For Each myPivot In WS.PivotTables Debug.Print myPivot.Name, myPivot.SourceData myString = Replace(myPivot.SourceData, OldFile, NewFile) myPivot.SourceData = myString Next myPivot Next WS End Sub -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "AndrewEdmunds" wrote: Hello and thanks for your reply, I'll try to explain what I mean as it is complicated. I have the financial statements for each of the 26 facilities for September. In a separate workbook, I created a pivot table to consolidate the 26 facilities. For October, I copy all of the facility files from September into an October folder and update the files with October's information. If I copy the pivot table file into the October folder the source data still points to the September files. How can I get it to point to October without having to go through and resetup each link? Thanks, -- Thank You Andrew Edmunds "Barb Reinhardt" wrote: I'm guessing you could define a dynamic named range for the Pivot Source. It's far easier to give a suggestion when you know how the source data is set up. -- HTH, Barb Reinhardt "AndrewEdmunds" wrote: Hello, I am using a pivot table to consolidate the financial statements of 26 facilities. How do I roll forward the pivot tables to point at the next month's facility files without having to recreate a new pivot table each month? Thanks -- Thank You Andrew Edmunds |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table data source | Excel Worksheet Functions | |||
Pivot Table Source Data | Excel Discussion (Misc queries) | |||
Pivot Table Source Data | Excel Discussion (Misc queries) | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions | |||
Pivot Table Source Data | New Users to Excel |