Creating a Refresh button in Excel

Question

i have the following macro setup for my refresh button, the problem is that it does not seem to work every time to refresh the results page which contains my summary data and my pivot tables.

Anyone see anything blatantly wrong?

The Macro i have setup is as follows:

Sub Macro1()

'

' Macro1 Macro

'

On Error Resume Next

    Response = Application.InputBox("Please enter in the Start Date as: MM/DD/YYYY", "Start Date", Range("A1").Value, 50, 150, "", , 1)

    If Response <> False Then

       ActiveSheet.Range("A1").Value = Response

    Else

        MsgBox ("Exiting Input! No Calculation will take place")

        Exit Sub

    End If

    Response = Application.InputBox("Please enter in the End Date as: MM/DD/YYYY", "End Date", Range("A2").Value, 50, 150, "", , 1)

    If Response <> False Then

       ActiveSheet.Range("A2").Value = Response

    Else

        MsgBox ("Exiting Input! No Calculation will take place")

        Exit Sub

    End If

'

    ActiveWorkbook.RefreshAll

    Calculate

'

    Sheets("Result Page").Select

    ActiveSheet.PivotTables("PivotTable-1").RefreshTable

    ActiveSheet.PivotTables("PivotTable-2").RefreshTable

    ActiveSheet.PivotTables("PivotTable-3").RefreshTable

    ActiveSheet.PivotTables("PivotTable-4").RefreshTable

    ActiveSheet.PivotTables("PivotTable-5").RefreshTable

    ActiveSheet.PivotTables("PivotTable-6").RefreshTable

    ActiveSheet.PivotTables("PivotTable-1-W").RefreshTable

    ActiveSheet.PivotTables("PivotTable-1-S").RefreshTable

    ActiveSheet.PivotTables("PivotTable-2-W").RefreshTable

    ActiveSheet.PivotTables("PivotTable-2-S").RefreshTable

'    ActiveSheet.PivotTables("PivotTable-3-W").RefreshTable

'    ActiveSheet.PivotTables("PivotTable-4-W").RefreshTable

    ActiveSheet.PivotTables("PivotTable-5-W").RefreshTable

    ActiveSheet.PivotTables("PivotTable-6-W").RefreshTable

    ActiveSheet.PivotTables("PivotTable-6-S").RefreshTable

    Sheets("Result Page").F9

    Sheets("Result Page").Refresh

End Sub

Answer 1

Nothing blatant, but the use of On Error Resume Next means that you will not see an error if it occurs.

Try without that and if you get an error and you don't understand why, tell us more - the error message and the line on which it occurs.

I would expect there to be an error on Sheets("Result Page").F9 as I don't think such a method exists.  Did you mean .Calculate ?

You could stick a Debug.Print "Macro1 ended" at the end of the macro just to make sure that it does indeed execute fully.

Answer 2

Ok i thought this was solved but my pivot tables are still not refreshing... i have to manually go to each one and refresh them manually.. they are not refreshing from the macro..

Sub Macro1()  

'  

' Macro1 Macro  

'  

    Response = Application.InputBox("Please enter in the Start Date as: MM/DD/YYYY", "Start Date", Range("A1").Value, 50, 150, "", , 1)  

    If Response <> False Then  

       ActiveSheet.Range("A1").Value = Response  

    Else  

        MsgBox ("Exiting Input! No Calculation will take place")  

        Exit Sub  

    End If  

    Response = Application.InputBox("Please enter in the End Date as: MM/DD/YYYY", "End Date", Range("A2").Value, 50, 150, "", , 1)  

    If Response <> False Then  

       ActiveSheet.Range("A2").Value = Response  

    Else  

        MsgBox ("Exiting Input! No Calculation will take place")  

        Exit Sub  

    End If  

'  

    ActiveWorkbook.RefreshAll  

    ActiveWorkbook.Calculate  

'  

    Sheet1.PivotTables("PivotTable-DW").RefreshTable  

    Sheet1.PivotTables("PivotTable-MERCER").RefreshTable  

    Sheet1.PivotTables("PivotTable-AUTF").RefreshTable  

    Sheet1.PivotTables("PivotTable-HOMF").RefreshTable  

    Sheet1.PivotTables("PivotTable-FPIC").RefreshTable  

    Sheet1.PivotTables("PivotTable-iPartners").RefreshTable  

    Sheet1.PivotTables("PivotTable-DW-Written").RefreshTable  

    Sheet1.PivotTables("PivotTable-DW-Surcharge").RefreshTable  

    Sheet1.PivotTables("PivotTable-Mercer-Written").RefreshTable  

    Sheet1.PivotTables("PivotTable-Mercer-Surcharge").RefreshTable  

'    Sheet1.PivotTables("PivotTable-AUTF-Written").RefreshTable  

'    Sheet1.PivotTables("PivotTable-HOMF-Written").RefreshTable  

    Sheet1.PivotTables("PivotTable-FPIC-Written").RefreshTable  

    Sheet1.PivotTables("PivotTable-iPartners-Written").RefreshTable  

    Sheet1.PivotTables("PivotTable-iPartners-Surcharge").RefreshTable  

    Sheet1.Activate  

    ActiveWorkbook.RefreshAll  

    Sheet1.Calculate  

'    ActiveSheet.RefreshAll  

'    Calculate  

'    Sheets("Result Page").F9  

    Debug.Print "Macro1 ended"  

End Sub

Answer 3

Bill,

Thanks for your help.. removing the ON Error line pointed me to what i needed to fix.  Replacing the activesheet with sheet1 solved most of the issues.. but there were also some issues with the names of the pivot tables, some had extra characters in them that i didn't notice before.. 

But the biggest help was removing the ON ERROR line...

All is functioning now.

Thanks,

Tom

Answer 4

So, on the line which is highlighted when you click Debug, the name you have used for the pivot table is incorrect.

Answer 5

I removed the On Error Resume Next Message and when running the macro i get

Run-Time Error '1004':

Unable to get the PivotTables Property of the Worksheet class