Pivot Table Automation to PDF's with Filter Changes

L

lwkochis

New Member
Joined
Oct 22, 2013
Messages
12
Hi, </SPAN>

I am working on an Excel spreadsheet with a few pivot tables. The end goal is to have 53 PDF's of the spreadsheet (Sheet3), stored in a folder, with the filename as the Organization </SPAN>name that is reflected in the pivot table. I will be doing this monthly so the variables in the pivot table will be Month Completed</SPAN>, Organization</SPAN>, and Year Completed</SPAN>. I would like to be able to tell Excel what these values are and then have it make a 53 PDF’s for each organization. Each PDF has to reflect a different organization. The pivot tables are in Sheet 3 and Data for Sheet1 Chart 1. </SPAN>
I also have two arrays on sheet, Data for Sheet1 Chart 1 in ranges: N10:P10 and Y10:AA10. When I write the organization in cells N9 and Y9, it returns data in those above ranges. VBA automation would nice for those as well if possible.
</SPAN>
I have a list of all of the organizations in a worksheet called organizations which reflects what is in the pivot table (maybe that is helpful for you). </SPAN>
I cannot attach the Workbook but I place the VBA code below. I am pretty good with VBA and if shown one example I can figure out the rest. </SPAN>

Thanks.

Lucas




Sub Allentown()
'
' Allentown Macro
'
'
'CHANGE TITLE TO ALLENTOWN
Range("B1").Select
ActiveCell.FormulaR1C1 = "LEARNING & DEVELOPMENT - ALLENTOWN"
Range("G5").Select
'TOP 10 EMPLOYEES WHO COMPLETED COURSES
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Data].[Organization].[Organization]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Data].[Organization].[Organization]").CurrentPageName = _
"[Data].[Organization].&[Allentown]"
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Data].[Month Completed].[Month Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Data].[Month Completed].[Month Completed]").CurrentPageName = _
"[Data].[Month Completed].&[9]"
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Data].[Year Completed].[Year Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable7").PivotFields( _
"[Data].[Year Completed].[Year Completed]").CurrentPageName = _
"[Data].[Year Completed].&[2013]"
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3

'MOST COURSES COMPLETED BY AN EMPLOYEE

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Data].[Organization].[Organization]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Data].[Organization].[Organization]").CurrentPageName = _
"[Data].[Organization].&[Allentown]"
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Data].[Month Completed].[Month Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Data].[Month Completed].[Month Completed]").CurrentPageName = _
"[Data].[Month Completed].&[9]"
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Data].[Year Completed].[Year Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Data].[Year Completed].[Year Completed]").CurrentPageName = _
"[Data].[Year Completed].&[2013]"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1

'COURSES IN PROGESS, REGISTERED, COMPLETED

Sheets("Data for Sheet1 Chart 1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Data].[Organization].[Organization]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Data].[Organization].[Organization]").CurrentPageName = _
"[Data].[Organization].&[Allentown]"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4

'ALL COURSES

ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"[Data].[Month Completed].[Month Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"[Data].[Month Completed].[Month Completed]").CurrentPageName = _
"[Data].[Month Completed].&[9]"

'ALL COURSES (TOP 10)

ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Data].[Month Completed].[Month Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Data].[Month Completed].[Month Completed]").CurrentPageName = _
"[Data].[Month Completed].&[9]"
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4

'ARRAY SEARCH

Range("N9:P10").Select
ActiveCell.FormulaR1C1 = "Allentown"
Range("O7").Select
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11

'NON-REQUIRED COURSES

ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[Data].[Month Completed].[Month Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"[Data].[Month Completed].[Month Completed]").CurrentPageName = _
"[Data].[Month Completed].&[9]"
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14

'NON-REQUIRED COURSES (TOP 10)

ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"[Data].[Month Completed].[Month Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"[Data].[Month Completed].[Month Completed]").CurrentPageName = _
"[Data].[Month Completed].&[9]"

'ARRAY SEARCH

Range("Y9:AA9").Select
ActiveCell.FormulaR1C1 = "Allentown"
Range("Z7").Select
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23


'TOP 10 EMPLOYEES WHO COMPLETED COURSES

ActiveSheet.PivotTables("PivotTable6").PivotFields( _
"[Data].[Organization].[Organization]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable6").PivotFields( _
"[Data].[Organization].[Organization]").CurrentPageName = _
"[Data].[Organization].&[Allentown]"
ActiveSheet.PivotTables("PivotTable6").PivotFields( _
"[Data].[Month Completed].[Month Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable6").PivotFields( _
"[Data].[Month Completed].[Month Completed]").CurrentPageName = _
"[Data].[Month Completed].&[9]"
ActiveSheet.PivotTables("PivotTable6").PivotFields( _
"[Data].[Year Completed].[Year Completed]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable6").PivotFields( _
"[Data].[Year Completed].[Year Completed]").CurrentPageName = _
"[Data].[Year Completed].&[2013]"
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("Sheet3").Select


Dim FileName As String
If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "There is more than one sheet selected," & vbNewLine & _
"and every selected sheet will be published."
End If
'Replace numSheets with the number of worksheets that will be saved as PDF
For x = 1 To 1
Sheets("WorksheetNames").Select
ThisSheet = ActiveSheet.Range("A" & x).Value
Sheets(ThisSheet).Select
'Call the function with the correct arguments
FileName = RDB_Create_PDF(Sheets(ThisSheet), "C:\Users\lkochis\Desktop\LL\ " & "Shelton" & ".pdf", True, True)
If FileName <> "" Then
'Ok, you find the PDF where you saved it
Else
MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
"Microsoft Add-in is not installed" & vbNewLine & _
"You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
"The path to Save the file in arg 2 is not correct" & vbNewLine & _
"You didn't want to overwrite the existing PDF if it exist"
End If
Next x
End Sub
Function RDB_Create_PDF(Myvar As Object, FixedFilePathName As String, _
OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean) As String
Dim FileFormatstr As String
Dim Fname As Variant
'Test If the Microsoft Add-in is installed
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
If FixedFilePathName = "" Then
'Open the GetSaveAsFilename dialog to enter a file name for the pdf
FileFormatstr = "PDF Files (*.pdf), *.pdf"
Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
Title:="Create PDF")
'If you cancel this dialog Exit the function
If Fname = False Then Exit Function
Else
Fname = FixedFilePathName
End If
'If OverwriteIfFileExist = False we test if the PDF
'already exist in the folder and Exit the function if that is True
If OverwriteIfFileExist = False Then
If Dir(Fname) <> "" Then Exit Function
End If
'Now the file name is correct we Publish to PDF
On Error Resume Next
Myvar.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=Fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
'If Publish is Ok the function will return the file name
If Dir(Fname) <> "" Then RDB_Create_PDF = Fname
End If
End Function


</SPAN>
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

G
  • Question
VBA Filter Pivot Table Question
Replies
0
Views
177
gmazza76
G
C
  • Question
Creating a button to select from a Pivot table filter
Replies
1
Views
478
Micron
M
S
  • Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
674
Steve48
S
S
  • Question
Keep getting an "Error 1004: item could not be found in the OLAP cube" when applying the filter to pivot table
Replies
5
Views
1K
RoryA
RoryA
B
  • Solved
SKIP PIVOT TABLE CREATION - NO DATA
Replies
6
Views
558
blaisjoel
B

Forum statistics

Threads
1,216,098
Messages
6,128,812
Members
449,468
Latest member
AGreen17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top