nirvehex
07-23-2014, 05:05 AM
Hello,
I have a code which creates a pivot table and works fine. Unless Row 1 is blank. Ideally, I need row 1 blank because I have a button there that brings you to a different tab.
I have created pivot tables from source data that is not at the top row. I think my code needs to start in row 2 for selecting the pivot source data. I'm just not sure how to make it do that.
Any idea why this is happening and how to fix?
Thanks.
Code:
Sub MakePivotTable()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("WorkOrders")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Pivot")
Dim PTCache As PivotCache
Dim PRange As Range
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="SSPivot")
' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the row fields
With pt.PivotFields("Material")
.Orientation = xlRowField
.Position = 1
End With
With pt.PivotFields("Equipment")
.Orientation = xlRowField
.Position = 2
End With
' Set up the data fields
With pt.PivotFields("Service On")
.Orientation = xlDataField
.Function = xlMax
.Position = 1
End With
With pt.PivotFields("Service On")
.Orientation = xlDataField
.Function = xlMin
.Position = 2
End With
With pt.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlCount
.Position = 3
End With
With pt.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlSum
.Position = 4
End With
With pt.PivotFields("Cost")
.Orientation = xlDataField
.Function = xlSum
.Position = 5
End With
With pt.PivotFields("Data")
.Orientation = xlColumnField
End With
' Now calc the pivot table
pt.ManualUpdate = False
End Sub
I have a code which creates a pivot table and works fine. Unless Row 1 is blank. Ideally, I need row 1 blank because I have a button there that brings you to a different tab.
I have created pivot tables from source data that is not at the top row. I think my code needs to start in row 2 for selecting the pivot source data. I'm just not sure how to make it do that.
Any idea why this is happening and how to fix?
Thanks.
Code:
Sub MakePivotTable()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("WorkOrders")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Pivot")
Dim PTCache As PivotCache
Dim PRange As Range
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="SSPivot")
' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the row fields
With pt.PivotFields("Material")
.Orientation = xlRowField
.Position = 1
End With
With pt.PivotFields("Equipment")
.Orientation = xlRowField
.Position = 2
End With
' Set up the data fields
With pt.PivotFields("Service On")
.Orientation = xlDataField
.Function = xlMax
.Position = 1
End With
With pt.PivotFields("Service On")
.Orientation = xlDataField
.Function = xlMin
.Position = 2
End With
With pt.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlCount
.Position = 3
End With
With pt.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlSum
.Position = 4
End With
With pt.PivotFields("Cost")
.Orientation = xlDataField
.Function = xlSum
.Position = 5
End With
With pt.PivotFields("Data")
.Orientation = xlColumnField
End With
' Now calc the pivot table
pt.ManualUpdate = False
End Sub