Hey guys, so i had recorded some code and made my own tweaks to lessen it. i know there is a bunch more i could do to make it run faster and was hoping i could get any and all suggestions! please and thank you, it'll also help me learn how to code better if i could see more examples! each campaign has multiple offices and i break them out weekly, so this is for one such office. over all, i copy the original master sheet of info onto a new sheet, break it down to just that office, change the cache for the pivots and refresh the data that then saves itself to the file location i need it to.
Recorded code:
Private Sub CommandButton11_Click()
Sheets(Array("Snapshot", "Cancel Wheel", "Agent Cancels", _
"Agent Lost Bonus", "Owner Lost Bonus", "Trending Data", "CPS Links")).Copy
Call savePanAtlantic
ActiveSheet.PivotTables("PivotTable5").PivotSelect "'Non-Profit Name'[All]", _
xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Trending Data!C1:C52", Version:=xlPivotTableVersion15)
Sheets("Cancel Wheel").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Pledge Snapshot!PivotTable5")
Sheets("Agent Cancels").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Pledge Snapshot!PivotTable5")
Sheets("Agent Lost Bonus").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Pledge Snapshot!PivotTable5")
Sheets("Owner Lost Bonus").Select
ActiveSheet.PivotTables("PivotTable5").PivotSelect "SupplierStatusReason[All]" _
, xlLabelOnly, True
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Snapshot!PivotTable5")
Sheets("Trending Data").Select
ActiveSheet.Range("$A:$AZ").AutoFilter Field:=4, Criteria1:=Array( _
"Altavista Group Inc", "B&G Marketing Concepts", "Blue Group Inc,", "CAM Partners" _
, "FBC", "Frontier Promotions Inc", "InStile Acquisitions Inc", _
"Marketing on 6th USA", "Altavista Group 2", "Pan Atlantic - Givebridge", _
"Pan Atlantic Associates", "Polaris Communications Group Inc", _
"The Plato Group Miami", "Worldwide Concepts Inc.", "="), Operator:= _
xlFilterValues
Worksheets("Trending Data").Range("A2").Select
Worksheets("Trending Data").Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Sheets(Array("Trending Data", "CPS Links")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Snapshot").Select
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
End Sub
once again, thank you for the help! hopefully ill start getting better at this!
Recorded code:
Private Sub CommandButton11_Click()
Sheets(Array("Snapshot", "Cancel Wheel", "Agent Cancels", _
"Agent Lost Bonus", "Owner Lost Bonus", "Trending Data", "CPS Links")).Copy
Call savePanAtlantic
ActiveSheet.PivotTables("PivotTable5").PivotSelect "'Non-Profit Name'[All]", _
xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Trending Data!C1:C52", Version:=xlPivotTableVersion15)
Sheets("Cancel Wheel").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Pledge Snapshot!PivotTable5")
Sheets("Agent Cancels").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Pledge Snapshot!PivotTable5")
Sheets("Agent Lost Bonus").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Pledge Snapshot!PivotTable5")
Sheets("Owner Lost Bonus").Select
ActiveSheet.PivotTables("PivotTable5").PivotSelect "SupplierStatusReason[All]" _
, xlLabelOnly, True
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Snapshot!PivotTable5")
Sheets("Trending Data").Select
ActiveSheet.Range("$A:$AZ").AutoFilter Field:=4, Criteria1:=Array( _
"Altavista Group Inc", "B&G Marketing Concepts", "Blue Group Inc,", "CAM Partners" _
, "FBC", "Frontier Promotions Inc", "InStile Acquisitions Inc", _
"Marketing on 6th USA", "Altavista Group 2", "Pan Atlantic - Givebridge", _
"Pan Atlantic Associates", "Polaris Communications Group Inc", _
"The Plato Group Miami", "Worldwide Concepts Inc.", "="), Operator:= _
xlFilterValues
Worksheets("Trending Data").Range("A2").Select
Worksheets("Trending Data").Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Sheets(Array("Trending Data", "CPS Links")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Snapshot").Select
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
End Sub
once again, thank you for the help! hopefully ill start getting better at this!