Question about this code

J

jcaptchaos2

Well-known Member
Joined
Sep 24, 2002
Messages
1,032
Office Version
  1. 365
Platform
  1. Windows
Hello,

I created this code so it would be easier to update these pivot tables, all I have been doing is using the replace option and changing the week number from in this case it was from 13 to 14 and it refers to the week number. I just noticed that even though all of the numbers in the pivot table showed week 14 but the data in the table was for week 13. I have the week number in the filter portion of the pivot and when I click on the drop down box I now see 14 twice in there? Hope I am explaining the issue so you understand it.


Code: 
Sub change_month_on_pivot_table()
'
' change_month_on_pivot_table Macro
'

'
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Week").CurrentPage = "14"
    Range("D13").Select
    Sheets("1019 Perato").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable10").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable9").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable8").PivotFields("Week").CurrentPage = "14"
    Sheets("1067 Perato").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable12").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable13").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable11").PivotFields("Week").CurrentPage = "14"
    Sheets("1069 Perato").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable16").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable15").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable14").PivotFields("Week").CurrentPage = "14"
    Sheets("1071 Perato").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable19").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable18").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable17").PivotFields("Week").CurrentPage = "14"
    Sheets("1073 Perato").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable22").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable21").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable20").PivotFields("Week").CurrentPage = "14"
    Sheets("1077 Perato").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable25").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable24").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable23").PivotFields("Week").CurrentPage = "14"
    Sheets("1079 Perato").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable28").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable27").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable26").PivotFields("Week").CurrentPage = "14"
    Sheets("1202 Perato").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable31").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable30").PivotFields("Week").CurrentPage = "14"
    ActiveSheet.PivotTables("PivotTable29").PivotFields("Week").CurrentPage = "14"
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
You must log in or register to reply here.

Similar threads

G
  • Question Question
VBA Filter Pivot Table Question
Replies
0
Views
475
gmazza76
G
B
  • Solved
Recording macro pivot table error
Replies
2
Views
1K
biglb79
B
B
  • Question Question
VBA code to create a pivot chart on active sheet
Replies
0
Views
3K
boforiamanfo
B
M
  • Question Question
Error Code '1004' when attempting to filter pivot table
Replies
0
Views
1K
MHamid
M
R
  • Question Question
Pivot table manipulation VBA code
Replies
5
Views
768
rajaniesh
R

Forum statistics

Threads
1,224,527
Messages
6,179,351
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top