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