Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > simplify code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default simplify code
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,624
Default simplify code
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


In article ,
C02C04 wrote:

I'm developing a report and have put some very simple codes together. Can
someone help to simplify?
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
Avi Avi is offline
external usenet poster
  
Posts: 29
Default simplify code
'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



On Feb 3, 9:17*pm, JE McGimpsey wrote:
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

In article ,

*C02C04 wrote:
I'm developing a report and have put some very simple codes together. Can
someone help to simplify?

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default simplify code
Thanks Barb, JE and Avi for your coding.

"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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default simplify code
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?

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I simplify a VB code?????? hoyos Excel Discussion (Misc queries) 1 November 6th 09 09:06 PM
Simplify code Inkel Excel Worksheet Functions 3 March 27th 09 05:52 PM
Need to simplify code alexwren Excel Discussion (Misc queries) 7 August 15th 06 08:07 PM
Simplify Code Soniya[_4_] Excel Programming 3 August 9th 06 06:55 PM
Help to simplify code. Michael Beckinsale Excel Programming 0 September 2nd 03 10:26 AM


All times are GMT +1. The time now is 02:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement