multiple workbooks & pivot tables

[comicwizard]

I have 20 separate workbooks and each have a pivot table. I would like to write code to open each workbook, update the pivot, save the workbook and close the workbook instead of me having to do that manually to each one.

I can place them all in the same folder to make things easier.

Ideally what I would like to do is have a master workbook with a list of the reports, I check the opens that need to be updated and fire off the code. Then the code does as described above and then updates a field on this master workbook once each one is completed.

I saw this done with a coworker but he will not share his code. So I am thinking he got this from someone else and doesn't understand the code enough and won't admit that.

So any help will help, I am really having a hard time building this myself.

 

[Superstar31]

I have 20 separate workbooks and each have a pivot table. I would like to write code to open each workbook, update the pivot, save the workbook and close the workbook instead of me having to do that manually to each one.

I can place them all in the same folder to make things easier.

Ideally what I would like to do is have a master workbook with a list of the reports, I check the opens that need to be updated and fire off the code. Then the code does as described above and then updates a field on this master workbook once each one is completed.

I saw this done with a coworker but he will not share his code. So I am thinking he got this from someone else and doesn't understand the code enough and won't admit that.

So any help will help, I am really having a hard time building this myself.

I know very little about macros

but i googled this.. first step is the ability to open the other workbook

Sub OpenUp()
Workbooks.Open("C:\MyFolder\MyBook.xls")
End Sub

or

Dim wb As Workbook
Set wb = Workbooks.Open("C\Test\test,xls")


From here

http://www.mrexcel.com/forum/excel-questions/648999-macro-open-another-excel-file.html

2nd step is to make that workbook active

(you can find hints here: http://stackoverflow.com/questions/...one-workbook-make-changes-to-another-workbook)

3rd step would be to create your own macro that updates pivot and paste that macro inside your already existing macro with a couple of tweaks!

Maybe this: Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Me.PivotTables("PivotTable1").PivotCache.Refresh
Application.EnableEvents = True
End Sub

from here: http://www.mrexcel.com/forum/excel-questions/545597-macro-auto-refresh-pivot-table-filter.html