simplify code

C

C02C04

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
 
B

Barb Reinhardt

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.
 
J

JE McGimpsey

Untested:

Public Sub refresh_Charts()
Dim ws As Worksheet
Dim vUsers As Variant
Dim vTables As Variant
Dim i As Long

vTables = Array("Program", "Origin", "DestRegion", "LSP")
With Worksheets("User")
vUsers = .Range("H10:H13").Value
For Each ws In Worksheets(Array("60D_Trend", _
"Month_Trend", "CT_60Days", "DestMix_60D"))
With ws.PivotTables("PivotTable1")
For i = 0 To 3
With .PivotFields(vTables(i))
.ClearAllFilters
.CurrentPage = vUsers(1, i + 1)
End With
Next i
End With
Next ws
.Select
End With
End Sub
 
A

Avi

'you can also try this modular approach - (untested)

Public sht60DTrnd As Worksheet
Public shtMonthTrnd As Worksheet
Public shtUsers As Worksheet
Public shtCT60Days As Worksheet
Public shtDestMix60D As Worksheet

'This is the main function and should be called first
Sub refresh_Charts()

Dim arrUsers As Variant
Call setSheetNames
arrUsers = shtUsers.Range("H10:H13")

Application.ScreenUpdating = False

Call ClearFilters(sht60DTrnd, "PivotTable1")
Call ApplyFilter(sht60DTrnd, "PivotTable1", arrUsers)

Call ClearFilters(shtMonthTrnd, "PivotTable1")
Call ApplyFilter(shtMonthTrnd, "PivotTable1", arrUsers)

Call ClearFilters(shtCT60Days, "PivotTable1")
Call ApplyFilter(shtCT60Days, "PivotTable1", arrUsers)

Call ClearFilters(shtDestMix60D, "PivotTable1")
Call ApplyFilter(shtDestMix60D, "PivotTable1", arrUsers)


shtUsers.Select
Application.ScreenUpdating = True

End Sub

' Procedure to set all sheet names
Sub setSheetNames()

Set sht60DTrnd = Sheets("60D_Trend")
Set shtMonthTrnd = Sheets("Month_Trend")
Set shtUsers = Sheets("User")
Set shtCT60Days = Sheets("CT_60Days")
Set shtDestMix60D = Sheets("DestMix_60D")

End Sub

' Procedure to clear all filters in selected pivot table
Sub ClearFilters(shtObj As Worksheet, strPvtName As String)

shtObj.Select
For Each Field In shtObj.PivotTables(strPvtName).PivotFields
Field.ClearAllFilters
Next

End Sub

' Procedure to apply filters in selected pivot table
Sub ApplyFilter(shtObj As Worksheet, strPvtName As String, arrUsers As
Variant)

shtObj.Select
With shtObj.PivotTables(strPvtName)
.PivotFields("Program").CurrentPage = arrUsers(0)
.PivotFields("Origin").CurrentPage = arrUsers(1)
.PivotFields("DestRegion ").CurrentPage = arrUsers(2)
.PivotFields("LSP").CurrentPage = arrUsers(3)
End With

End Sub
 
C

C02C04

Thanks Barb, JE and Avi for your coding.

C02C04 said:
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
Click to expand...
 
C

C02C04

This looks really cool and impressive. Every time the user hit the Refresh
Macro it actually updates 4 pivot charts on the “User†tab.

Here is part 2 of my request. Management wanted static reports, preferably
in ppt. How can I create a Macro where it pre-set the 4 parameters
(prog_user, origin_user, dest_region_user, lsp_user) to certain values,
updates the charts and copy (as pic) to ppt? Copy to another workbook with
multiple sheets is also fine. I can do a one-time manual link to ppt and then
break link.

Any suggestions?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

delete selection in pivotcolumn 3
Format Pivot total rows 2
Using macro to switch 'page' in a pivot table? 1
Programming a pivot 0
PivotTable 2003 Code is not working 0
Several Pivot Tables: Change one filter and adapt it for the other 2
Run-Time error 1004 Application defined or object defined error - 0
Pivot table consolidation with variable number of worksheets 0

Top