asked on

Excel Macro - to create Pivot and export to PDF

I need a MACRO to create a pivot Table and export that to a PDF or show as image in another tab then export it.

Worksheet name = 8 Week INC Trend
Report Filter I need , "8 Week Trend", "Task Type"
Column Labels I need "Week Ending"
Row Labels I need "Assigned to"
Values I need "number"

NOTE I do have a name range for my data TAB called "RANGE"

Norie

Can you upload a sample workbook so we can see how the data is structured on the sheet '8 Week INC Trend'?

PS I'm assuming that's the sheet where the source data for the pivot table is located.

Edward Pamias

ASKER

I cannot upload the data from the sheet. :( When I make a pivot, I click insert, pivot table, hit F3 to select named range "RANGE" then let it open a new tab to create pivot table on. I will try to send a screen shot of the columns.

Edward Pamias

ASKER

Edward Pamias

ASKER

Sorry it is so small but that's the best I can do with snipping tool

Norie

Edward

Have you tried recording a macro when you create a pivot table from the source data?

That would at least give you a start on things.

Shums Faruk

Edward,

Post a snip of manually Created Pivot.

Shums Faruk

What do you need of Values of Numbers?
Count or Sum?

Edward Pamias

ASKER

I need the told numbers - see snipit.

Shums Faruk

Try below which is a count of Numbers:

Sub CreatePivotAndPDF()
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With
'Delete Preivous Pivot Table Worksheet &amp; Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("8 Week INC Trend")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="FilteredPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="FilteredPivotTable")
'Insert Page Fields
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("8 Week Trend")
 .Orientation = xlPageField
 .Position = 1
End With
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Task Type")
    .Orientation = xlPageField
    .Position = 1
End With
'Insert Row Fields
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Assigned To")
    .Orientation = xlRowField
    .Position = 1
End With
'Insert Column Fields
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Week Ending")
    .Orientation = xlColumnField
    .Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Number")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    .NumberFormat = "#,##0"
    .Name = "Numbers"
End With
'Format Pivot Table
ActiveSheet.PivotTables("FilteredPivotTable").CompactLayoutRowHeader = "Assigned Person"
ActiveSheet.PivotTables("FilteredPivotTable").CompactLayoutColumnHeader = "Weeks"
ActiveSheet.PivotTables("FilteredPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("FilteredPivotTable").TableStyle2 = "PivotStyleMedium15"
ActiveSheet.Columns.AutoFit

With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & .Name, OpenAfterPublish:=True
End With

With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

Shums Faruk

What about the selection of Page Fields? Should it be Valid & Incident?

Edward Pamias

ASKER

When I create the pivot and add 8 week trend. The selections are blank field or Valid. I select valid so I can get just 8 weeks of totals. Then I drop weekend ending in the columns. I put assigned to, in Row Labels, and Numbers in the values section.

Shums Faruk

In this case, I would advice, you should have separate VBAs, one for creating Pivot, make the selection then run another vba for creating PDF.

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Edward Pamias

ASKER

The Data is on sheet Page 1. I have a feeling I need to change something. :(

Shums Faruk