I have created multiple command buttons that i need to make my life really easy. these were created by recording the macro and tweaking the results to what i needed. my final need is for it to automatically save as the value posted in a cell. the files have to be named a certain way so they can be uploaded into the company drives, which means spelling mistakes result in no-goes. So i had created a cell that updates the date that i need, and the unique name of the office each week. (There are over 60 of these, which is why ive been trying to automate them). I was able to create a save macro that works fine on its own, but it wont play with the recorded macro. is there a way i can include it? because it really makes my life super simple when everything is just one click! please and thank you for any and all advice! I am still fairly new to vba and am trying to learn how to do things but i am a slow man haha.
Basically im looking for a way to do a save as referencing a certain cell in my workbook on the CPS Links sheet cell A4.
Here is the code:
Private Sub CommandButton1_Click()
Sheets(Array("Sales Snapshot", "Cancellation Wheel", "Trending Cancellations", _
"Agent Cancels", "Agent Estimate Lost Profit", "Owner Estimate Lost Profit", _
"Trending Data", "CPS Links")).Copy
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Trending Data!C1:C52", Version:=xlPivotTableVersion15)
Sheets("Cancellation Wheel").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Trending Cancellations").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
ActiveSheet.PivotTables("PivotTable6").PivotSelect "PeriodName[All]", _
xlLabelOnly, True
ActiveSheet.PivotTables("PivotTable6").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Agent Cancels").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Agent Estimate Lost Profit").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Owner Estimate Lost Profit").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Trending Data").Select
ActiveSheet.Range("A:AZ").AutoFilter Field:=4, Criteria1:=Array( _
"Assurance International Inc", "Detroit Business Consulting Inc.", _
"Elite Advertising Inc.", "FBC", "Michigan Marketing Solutions, Inc.", _
"Modern Marketing Miami Inc", "Smith Advertising, Inc.", "The Plato Group Miami", _
"="), 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("Sales Snapshot").Select
ActiveWorkbook.RefreshAll
ActiveSheet.PivotTables("PivotTable5").PivotFields("OfficeName").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable5").PivotFields("OfficeName")
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable5").PivotFields("OfficeName"). _
EnableMultiplePageItems = True
Dim FName As String
Dim FPath As String
Dim wbBook1 As Workbook
Set wbBook1 = ActiveWorkbook
FPath = "T:\Client Services\Cancellation Wheels\Weekly Data\Comcast\"
FName = wbBook1.Sheets("CPS Links").Range("A4").Text
End Sub
Basically im looking for a way to do a save as referencing a certain cell in my workbook on the CPS Links sheet cell A4.
Here is the code:
Private Sub CommandButton1_Click()
Sheets(Array("Sales Snapshot", "Cancellation Wheel", "Trending Cancellations", _
"Agent Cancels", "Agent Estimate Lost Profit", "Owner Estimate Lost Profit", _
"Trending Data", "CPS Links")).Copy
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Trending Data!C1:C52", Version:=xlPivotTableVersion15)
Sheets("Cancellation Wheel").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Trending Cancellations").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
ActiveSheet.PivotTables("PivotTable6").PivotSelect "PeriodName[All]", _
xlLabelOnly, True
ActiveSheet.PivotTables("PivotTable6").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Agent Cancels").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Agent Estimate Lost Profit").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Owner Estimate Lost Profit").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ( _
"Sales Snapshot!PivotTable5")
Sheets("Trending Data").Select
ActiveSheet.Range("A:AZ").AutoFilter Field:=4, Criteria1:=Array( _
"Assurance International Inc", "Detroit Business Consulting Inc.", _
"Elite Advertising Inc.", "FBC", "Michigan Marketing Solutions, Inc.", _
"Modern Marketing Miami Inc", "Smith Advertising, Inc.", "The Plato Group Miami", _
"="), 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("Sales Snapshot").Select
ActiveWorkbook.RefreshAll
ActiveSheet.PivotTables("PivotTable5").PivotFields("OfficeName").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable5").PivotFields("OfficeName")
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable5").PivotFields("OfficeName"). _
EnableMultiplePageItems = True
Dim FName As String
Dim FPath As String
Dim wbBook1 As Workbook
Set wbBook1 = ActiveWorkbook
FPath = "T:\Client Services\Cancellation Wheels\Weekly Data\Comcast\"
FName = wbBook1.Sheets("CPS Links").Range("A4").Text
End Sub