Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data from several worksheets via pivot table
I'm not sure this is possible, but no harm in asking :o)
I have several workbooks in one folder that I need to consolidate via a pivot table in a separate workbook. The workbooks all contains one sheet for typing in data, one sheet with result-data and all are created on the same template. My questions a 1. If I add a new workbook in the folder, how do I update the pivot table? 2. Is it possible (maybe via a macro) to create the pivot table so that it gets data from all the workbooks in a fixed folder and so that if a workbook is added, it automatically updates? Alternative I may need to implement all the type-in sheets in one workbook for consolidating. 3. If I implement all the type-in sheets in one workbook for consolidating, is it possible automatically updates the pivot table (or. functions) in the same workbook? Hope someone out there have some answers or suggestions :o) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data from several worksheets via pivot table
Hi, Find answers in lines below.
"mthatt" wrote: I'm not sure this is possible, but no harm in asking :o) I have several workbooks in one folder that I need to consolidate via a pivot table in a separate workbook. The workbooks all contains one sheet for typing in data, one sheet with result-data and all are created on the same template. My questions a 1. If I add a new workbook in the folder, how do I update the pivot table? customize the pivotTable object as below. ActiveSheet.PivotTableWizard xlDatabase, Range("A1:C100") 2. Is it possible (maybe via a macro) to create the pivot table so that it gets data from all the workbooks in a fixed folder and so that if a workbook is added, it automatically updates? It can be done if all workbooks have same format. You can check the date of the workbooks to find the new additions or having a list of workbooks in a text file. You access the files in a folder as follows: Sub ShowFolderList(folderspec) Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.Files For Each f1 in fc s = s & f1.name s = s & vbCrLf Next MsgBox s End Sub Then add the code to modify the pivot table to include the new workbooks. Alternative I may need to implement all the type-in sheets in one workbook for consolidating. 3. If I implement all the type-in sheets in one workbook for consolidating, is it possible automatically updates the pivot table (or. functions) in the same workbook? You can create macros to execute at specific user action (ie. clicking a button, etc) or Excel event (opening or closing workbooks, etc). Hope someone out there have some answers or suggestions :o) The answer to your questions is yes there are ways to implement what you need, but sometimes the solution is not straight forward. It will require research and imagination. Carlos Lozano www.caxonline.net |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate data from several worksheets via pivot table
Hi Carlos
Im not sure how to implement the code. I've put the folderlist-code in the consolidated woorkbook, where my pivot also is. How do i get the pivot automatically to grab the filename and get the datas from the seperat woorkbokks i the specific folder? All the seperate woorkbooks is in same format and the datarange to be consolidatede are all called "data". As you describe it sounds fairly simple, but im still at novice i vba, so could you please be a bit more specific :o) Thanks. Michael "Carlos Lozano" wrote: Hi, Find answers in lines below. "mthatt" wrote: I'm not sure this is possible, but no harm in asking :o) I have several workbooks in one folder that I need to consolidate via a pivot table in a separate workbook. The workbooks all contains one sheet for typing in data, one sheet with result-data and all are created on the same template. My questions a 1. If I add a new workbook in the folder, how do I update the pivot table? customize the pivotTable object as below. ActiveSheet.PivotTableWizard xlDatabase, Range("A1:C100") 2. Is it possible (maybe via a macro) to create the pivot table so that it gets data from all the workbooks in a fixed folder and so that if a workbook is added, it automatically updates? It can be done if all workbooks have same format. You can check the date of the workbooks to find the new additions or having a list of workbooks in a text file. You access the files in a folder as follows: Sub ShowFolderList(folderspec) Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.Files For Each f1 in fc s = s & f1.name s = s & vbCrLf Next MsgBox s End Sub Then add the code to modify the pivot table to include the new workbooks. Alternative I may need to implement all the type-in sheets in one workbook for consolidating. 3. If I implement all the type-in sheets in one workbook for consolidating, is it possible automatically updates the pivot table (or. functions) in the same workbook? You can create macros to execute at specific user action (ie. clicking a button, etc) or Excel event (opening or closing workbooks, etc). Hope someone out there have some answers or suggestions :o) The answer to your questions is yes there are ways to implement what you need, but sometimes the solution is not straight forward. It will require research and imagination. Carlos Lozano www.caxonline.net |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidate/VLOOKUP/Pivot Table? | Excel Discussion (Misc queries) | |||
consolidate data in pivot table | Excel Discussion (Misc queries) | |||
Using Pivot Table to consolidate multiple worksheets | Excel Discussion (Misc queries) | |||
how do I consolidate multiple pivot tables into one pivot table? | Excel Discussion (Misc queries) | |||
Consolidate data from several worksheets via pivot table | Excel Worksheet Functions |