Refreshing all the pivot tables in my excel workbook with a macro

Yes.

ThisWorkbook.RefreshAll

Or, if your Excel version is old enough,

Dim Sheet as WorkSheet, Pivot as PivotTable
For Each Sheet in ThisWorkbook.WorkSheets
    For Each Pivot in Sheet.PivotTables
        Pivot.RefreshTable
        Pivot.Update
    Next
Next

This VBA code will refresh all pivot tables/charts in the workbook.

Sub RefreshAllPivotTables()

Dim PT As PivotTable
Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets

        For Each PT In WS.PivotTables
          PT.RefreshTable
        Next PT

    Next WS

End Sub

Another non-programatic option is:

• Right click on each pivot table
• Select Table options
• Tick the 'Refresh on open' option.
• Click on the OK button

This will refresh the pivot table each time the workbook is opened.

There is a refresh all option in the Pivot Table tool bar. That is enough. Dont have to do anything else.

Press ctrl+alt+F5

You have a PivotTables collection on a the VB Worksheet object. So, a quick loop like this will work:

Sub RefreshPivotTables()
    Dim pivotTable As PivotTable
    For Each pivotTable In ActiveSheet.PivotTables
        pivotTable.RefreshTable
    Next
End Sub

Notes from the trenches:

1. Remember to unprotect any protected sheets before updating the PivotTable.
2. Save often.
3. I'll think of more and update in due course... :)

Good luck!