Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Tables Not Refreshing (2003)

    Hi,
    I have an Excel spreadsheet "Sheet1" that I use as a template. What I do is from Access I select some data and export this data to excel using sheet1 as the template. It then creates a new sheet with the new data. I then have a pivot table that I want to refresh it's data. Basically what I did was create a macro that when run would refresh the pivot table and then print it.

    My problem is, because sheet1 is my template when I record my macro it records my PivotTable data range as sheet1!aa11:ad13, which is correct but when the new sheet is created from the access export, it still tries to look to sheet1 for the data and the data is on the newly created sheet. (the new sheets have the current days date as the sheet name)

    I can't figure out how to tell the pivot table to look to the current sheet for it's data, not sheet1. I had created a name range "VickiImpacTotal" but that wants to always put a sheet name with the range. So i figured I'd change the code below to just the range but I get an error message.

    ActiveSheet.PivotTables("VickiImpacTotal").PivotCa che.Refresh changed to
    ActiveSheet.PivotTables("AA11:AD13").PivotCache.Re fresh
    I get the error message: Unable to get the PivotTable property of the Worksheet class

    Any thoughts?
    thanks,
    Deb

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot Tables Not Refreshing (2003)

    If the exported data is added to a sheet that is always the first sheet in the workbook you can change the code to:
    Sheets(1).PivotTables("AA11:AD13").PivotCache.Refr esh
    Regards,
    Rudi

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables Not Refreshing (2003)

    It's not always the first sheet in the file. We use sheet1 as the template and then a new sheet 30Jun05 is created and it the 2nd sheet, then if I export tomorrow a new sheet 01Jul05 will be created and it will be the 3rd sheet. and so on. Sheet1 always stays blank, no data is ever added to that sheet.
    Thanks,
    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables Not Refreshing (2003)

    The argument to PivotTables(...) must either be the name of the pivot table, or an index number (1, 2, ...); you cannot use a range. If you only have one pivot table on the worksheet, you can use

    ActiveSheet.PivotTables(1).PivotCache.Refresh

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables Not Refreshing (2003)

    Thanks Hans,
    That got rid of the error message, but the pivot table did not update. it still wants to go to sheet1 (which is blank) to get the data list instead of the current worksheet.
    Thanks,
    Deb

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables Not Refreshing (2003)

    What code are you using to create the pivot table? Does that contain a reference to Sheet1?

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables Not Refreshing (2003)

    Yes, because when I create my macro I'm on sheet1 (my template). So the data list in the pivot table refers to sheet 1 even after it creates the new sheet. So I believe the problem is telling the newly created sheet to look to the current sheets data list, not sheet1 but I don't know how to do that.
    Deb

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables Not Refreshing (2003)

    If you show us what code you have now we may be able to suggest a modification.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pivot Tables Not Refreshing (2003)

    Hi,
    Assuming you are using the same pivot table each time, you need to change the datasource of that pivot table's pivotcache - e.g.:
    <code>ActiveSheet.PivotTables("VickiImpacTotal").P ivotCache.SourceData = "sheetnamehere!R11C27:R13C30"</code>
    (Note: this is air code so untested!)
    Hope that helps.

    PS If that's your source range (AA11:AD13), a pivot table seems a little like overkill!
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables Not Refreshing (2003)

    This is the code for the macro.
    Sub VickiPrint()
    '
    ' VickiPrint Macro
    ' Macro recorded 6/30/2005 by DSCR
    '

    '
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    Range("AA11").Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables(1).PivotCache.Refresh
    Range("AA11").Select
    Selection.Font.ColorIndex = 2
    Selection.Interior.ColorIndex = 2
    ActiveWindow.SmallScroll ToRight:=8
    Range("AA8:AD49").Select
    ActiveSheet.pagesetup.PrintArea = "$AA$8:$AD$49"
    With ActiveSheet.pagesetup
    .PrintTitleRows = "$1:$10"
    .PrintTitleColumns = ""
    End With
    ActiveSheet.pagesetup.PrintArea = "$AA$8:$AD$49"
    With ActiveSheet.pagesetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&8Page &P of &N"
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.04)
    .FooterMargin = Application.InchesToPoints(0.04)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 1200
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub

  11. #11
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables Not Refreshing (2003)

    Rory,
    The problem with your code is "sheetnamehere", I don't know what that sheet name will be. Everytime a person exports from Access it creates a new sheet with that days date. So the sheet name will change often. My data source is not aa11:ad13 that'st he range of the basic pivot table. My data source should be a10:m300.

    Is there a way under VickiImpacTotal (my named range) to put code or something there to say activesheet or whatever sheet i'm on, instead of it saying sheet1?
    Deb

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables Not Refreshing (2003)

    Does this work:

    ActiveSheet.PivotTables(1).PivotCache.SourceData = ActiveSheet.Name & "!A10:M300"

  13. #13
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables Not Refreshing (2003)

    I get this message: Application-defined or object-defined error and it highlights the line you gave me.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Tables Not Refreshing (2003)

    Where have you inserted the line?

  15. #15
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables Not Refreshing (2003)

    Sub VickiPrint()
    '
    ' VickiPrint Macro
    ' Macro recorded 6/30/2005 by DSCR
    '

    '
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    Range("AA11").Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    'ActiveSheet.PivotTables(1).PivotCache.Refresh
    ActiveSheet.PivotTables(1).PivotCache.SourceData = ActiveSheet.Name & "!A10:M300"
    Range("AA11").Select
    Selection.Font.ColorIndex = 2
    Selection.Interior.ColorIndex = 2
    ActiveWindow.SmallScroll ToRight:=8
    Range("AA8:AD49").Select
    ActiveSheet.pagesetup.PrintArea = "$AA$8:$AD$49"
    With ActiveSheet.pagesetup
    .PrintTitleRows = "$1:$10"
    .PrintTitleColumns = ""
    End With
    ActiveSheet.pagesetup.PrintArea = "$AA$8:$AD$49"
    With ActiveSheet.pagesetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&8Page &P of &N"
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.04)
    .FooterMargin = Application.InchesToPoints(0.04)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 1200
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

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