+ Reply to Thread
Results 1 to 7 of 7

Refreshing Pivot table problem?

  1. #1
    Registered User
    Join Date
    11-03-2006
    Posts
    12

    Refreshing Pivot table problem?

    I have the first page where people PASTE info into 4 columns and then on a second page a pivot table sorts and organizes the information. I have hidden the pivot table so users cannot screw it up and made links to the numbers in the pivot table in columns that they can see. My problem is that I need them to be able to refresh the pivot table once they have pasted their info in the first sheet. I know there is a Auto Refresh but they would have to save and open the file again for that to work.

    I need a button to refresh the pivot table?

    Can someone help?

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    There is one on the Pivot Table Menu. If you want, just copy that menu item onto a new menubar

  3. #3
    Registered User
    Join Date
    11-03-2006
    Posts
    12
    Many of my users are slow and it would be easier to make a button to Refresh the Pivot table...can this be done?

    Please help this is the only thing left and then I am completed my sheet Thanks

  4. #4
    Registered User
    Join Date
    11-03-2006
    Posts
    12
    I actually am looking to put a button on the Page to refresh the pivot table like a clear form button?

    anyone help please?

  5. #5
    Forum Contributor
    Join Date
    09-04-2006
    Location
    Yorkshire, England
    Posts
    267

    Reply

    Hi


    The best way to do this is through visual basic.

    In your worksheet press Alt-F11 to open the VBA editor (Or Tools > Macro > Visual Basic Editor). Look at the project window down the left hand side. You should see the name of your workbook next to VBA Project and various drop down boxes (You will also see any other workbooks you have open here). Look for a drop down box called Module. If you do not have one then click on the name of your vba project in the project window so it is highlighted in blue. Then Insert > Module. This should have created a drop down box called module which contains a blank module called Module1. Double click on Module1 (or anyother module if you have macros) and copy the below text into it.



    Sub one()
    Dim pt As PivotTable
    For Each pt In ActiveSheet.PivotTables
    pt.RefreshTable
    Next pt
    End Sub



    Save and close the editor down. Now create your button. I find it best to use the autoshapes toolbar to make buttons, if you cannot see it go View > Toolbars > Drawing. Look for the autoshapes drop down list and make your button. When you are done right click on the shape and choose assign macro. Look for the macro called one and click on it so it is highlighted and click OK. Now the button will run the macro to refresh all of your pivot tables.


    Hope it helps
    JR
    Versions
    Mac OS X 'Leopard'
    Mac MS Office Excel 2004
    Windows XP
    MS Excel 2002
    MS Excel 2003

  6. #6
    Forum Contributor
    Join Date
    09-04-2006
    Location
    Yorkshire, England
    Posts
    267
    Ah, still to early, just realised you said you have them on a second page.

    Try this code then:

    Sub one()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim pt As PivotTable
    For Each pt In Worksheets(" The name of your sheet").PivotTables
    pt.RefreshTable
    Next pt
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub

  7. #7
    Forum Contributor
    Join Date
    09-04-2006
    Location
    Yorkshire, England
    Posts
    267
    Sorry for the messing around, you also said that the second page was hidden:

    try:

    Sub one()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim pt As PivotTable
    Sheets(" The name of your second sheet").Visible = True
    For Each pt In Worksheets("The name of your second sheet").PivotTables
    pt.RefreshTable
    Next pt
    Sheets("The name of your second sheet").Visible = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub


    Hope it helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1