Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro that adds one row label

    Hi all,

    I would like your help if you don't mind. I have four pivot tables that need to be updated each week. I have a code below, but this code only shows the last 4 rows. I need a macro that adds a week (row label) to the existing pivot tables. E.g. if the pivot tables contain data from week 1,2,3,4. The following week I want update these tables by applying a macro so that they show data from week 1,2,3,4,5. The week after I want it to show data from week 1 to 6 and so forth.

    Sorry for the long explanation. Hopefully it's clear to you what I want.

    Thanks in advance,

    Code:
    Sub Update4()Dim i As Long, x As Long, sh As Worksheet
    Application.ScreenUpdating = False
    For Each sh In Sheets
     With sh
      If WorksheetFunction.Or(.Name = "Totaal") Then
        For x = 1 To .PivotTables.Count
            With .PivotTables(x).PivotFields("Week")
                For i = .PivotItems.Count - 3 To .PivotItems.Count
                  .PivotItems(i).Visible = True
                Next i
              .PivotItems(.PivotItems.Count - 4).Visible = False
             End With
          Next x
         End If
        End With
      Next sh
    End Sub

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 765 Times in 699 Posts
    BBT,

    Welcome to the Lounge as a new poster!

    I don't know the background of your question but give this a try!

    If you create a dynamic name range for each of your source ranges and create your Pivots using the dynamic range name then you can use this code to update them all.

    Code:
    Option Explicit
    
    Sub PTRefresh()
    
       Dim sht    As Worksheet
       Dim iPTCnt As Integer
       
       For Each sht In ActiveWorkbook.Worksheets
          For iPTCnt = 1 To sht.PivotTables.Count
             sht.PivotTables(iPTCnt).PivotCache.Refresh
           Next iPTCnt
       Next sht
    
    End Sub
    Sample workbook: DynamicPivotTables.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    BBT,

    Welcome to the Lounge as a new poster!

    I don't know the background of your question but give this a try!

    If you create a dynamic name range for each of your source ranges and create your Pivots using the dynamic range name then you can use this code to update them all.

    Code:
    Option Explicit
    
    Sub PTRefresh()
    
       Dim sht    As Worksheet
       Dim iPTCnt As Integer
       
       For Each sht In ActiveWorkbook.Worksheets
          For iPTCnt = 1 To sht.PivotTables.Count
             sht.PivotTables(iPTCnt).PivotCache.Refresh
           Next iPTCnt
       Next sht
    
    End Sub
    Sample workbook: DynamicPivotTables.xlsm

    HTH
    Thanks for the nice welcome

    Your macro does exactly what I want. However if I apply it to my example file it doesn't work. I named the source range 'Week' and tried to apply this range to my pivots. It gives me the following error: invalid reference.

    As you can see in the example file, I have now data from week 12 to 32. Next week I'm gonna add data from week 33 and I would like to have a macro that updates my pivots to week 12 to 33. The week after it needs to add another week and so on.

    I'm quite interested in VBA. I'm trying to read into it, but I find it difficult to understand; it's quite complex.

    Hope you can help me!

    Thanks in advance,
    Attached Files Attached Files

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 765 Times in 699 Posts
    BTB,

    Ok here's what you need to do.

    First, your range name was faulty.
    NameMgr.JPG
    Use this: =OFFSET('Data Overall'!$A$1,0,0,COUNTA('Data Overall'!$A$1:$A$3001),8)

    Next you need to change the Data Source (select pivot table -> Options -> Change Data Source) for each of your pivots to: week.
    datasource.JPG
    Note: you'll have to recreate some of the tables after doing this as some of the columns disappear!

    Once you do this the macro should work.

    Here's your sample with the first 2 PTs changed and updated with my macro.
    BTB example pivottable.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    New Lounger
    Join Date
    Aug 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much. Your help is appreciated. Naming the range and referring to it fixed the issue!

Posting Permissions

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