simplify code
Try something like this
Option Explicit
Sub refresh_Charts()
Dim prog_user As Variant
Dim origin_user As Variant
Dim dest_region_user As Variant
Dim lsp_user As Variant
Application.ScreenUpdating = False
prog_user = Worksheets("User").Range("H10")
origin_user = Worksheets("User").Range("H11")
dest_region_user = Worksheets("User").Range("H12")
lsp_user = Worksheets("User").Range("H13")
Set myWS = Sheets("60D_Trend")
Call ModifyPivot(myWS, prog_user, origin_user, dest_region, user, lsp_user)
Set myWS = Sheets("Month_Trend")
Call ModifyPivot(myWS, prog_user, origin_user, dest_region, user, lsp_user)
Set myWS = Sheets("CT_60Days")
Call ModifyPivot(myWS, prog_user, origin_user, dest_region, user, lsp_user)
Set myWS = Sheets("DestMix_60D")
Call ModifyPivot(myWS, prog_user, origin_user, dest_region, user, lsp_user)
Sheets("User").Select
Application.ScreenUpdating = True
End Sub
Sub ModifyPivot(myWS As Worksheet, prog_user As Variant, origin_user As
Variant, _
dest_region_user As Variant, lsp_user As Variant)
Dim myPivot As Excel.PivotTable
Set myPivot = myWS.PivotTables("PivotTable1")
myPivot.PivotFields("Program").ClearAllFilters
myPivot.PivotFields("Program").CurrentPage = prog_user
myPivot.PivotFields("Origin").ClearAllFilters
myPivot.PivotFields("Origin").CurrentPage = origin_user
myPivot.PivotFields("DestRegion ").ClearAllFilters ""
myPivot.PivotFields("DestRegion ").CurrentPage = dest_region_user
myPivot.PivotFields("LSP").ClearAllFilters
myPivot.PivotFields("LSP").CurrentPage = lsp_user
End Sub
This is UNTESTED.
--
HTH,
Barb Reinhardt
If this post was helpful to you, please click YES below.
"C02C04" wrote:
I'm developing a report and have put some very simple codes together. Can
someone help to simplify?
Sub refresh_Charts()
Application.ScreenUpdating = False
prog_user = Worksheets("User").Range("H10")
origin_user = Worksheets("User").Range("H11")
dest_region_user = Worksheets("User").Range("H12")
lsp_user = Worksheets("User").Range("H13")
Sheets("60D_Trend").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Program").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Program").CurrentPage =
prog_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Origin").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Origin").CurrentPage
= origin_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest
Region").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest
Region").CurrentPage = dest_region_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("LSP").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("LSP").CurrentPage =
lsp_user
Sheets("Month_Trend").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Program").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Program").CurrentPage =
prog_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Origin").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Origin").CurrentPage
= origin_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest
Region").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest
Region").CurrentPage = dest_region_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("LSP").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("LSP").CurrentPage =
lsp_user
Sheets("CT_60Days").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Program").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Program").CurrentPage =
prog_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Origin").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Origin").CurrentPage
= origin_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest
Region").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest
Region").CurrentPage = dest_region_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("LSP").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("LSP").CurrentPage =
lsp_user
Sheets("DestMix_60D").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Program").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Program").CurrentPage =
prog_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Origin").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Origin").CurrentPage
= origin_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest
Region").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest
Region").CurrentPage = dest_region_user
ActiveSheet.PivotTables("PivotTable1").PivotFields ("LSP").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields ("LSP").CurrentPage =
lsp_user
Sheets("User").Select
Application.ScreenUpdating = True
End Sub
|