I have created VBA code to create a pivot table. When I run through it with the Play button in VBA Editor, it works perfectly. When I created a button to run the macro it is stopping with Run-time Error '91': Object Variable or With Block variable not set. When I run the Debug it goes to the section I am setting up the PivotFields and highlights the first one "With .PivotFields("GAAP Cntrct").
I am new to VBA so any help would be greatly appreciated!!!
Sub PivotTable()
''''''''''''''''Current EAC Pivot Table''''''''''''''''''''
Dim wksData As Worksheet
Dim wksDest As Worksheet
Dim rngData As Range
Dim pvtTable As PivotTable
Dim DataFldCount As Long
Dim pvtFld As PivotField
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim EAC_Model As Workbook
Dim EACModel As Variant
Dim EAC_Element As Integer
EACModel = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*", Title:="Please select your EAC Model")
Workbooks.Open (EACModel)
Set EAC_Model = ActiveWorkbook
'Opens EAC File
On Error Resume Next
Worksheets("EAC Detail - Ops EAC (2)").Delete
On Error GoTo 0
On Error Resume Next
Worksheets("Analysis Table").Delete
On Error GoTo 0
'Removes prior analysis
Sheets("EAC Detail - Ops EAC").Select
Sheets("EAC Detail - Ops EAC").Copy Before:=Sheets("EAC Process Information -->")
Columns("N:AA").Select
Selection.EntireColumn.Hidden = False
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("EAC Detail - Acctg Adj").Select
Columns("N:AA").Select
Selection.EntireColumn.Hidden = False
Rows("4:4").Select
Selection.AutoFilter
Range("M8").Select
ActiveSheet.Range("$A$4:$CZ$49").AutoFilter Field:=13, Criteria1:= _
"=Contract Loss Exp", Operator:=xlOr, Criteria2:="=Revenue Adjustment"
Range("A1048576").Select
Selection.End(xlUp).Select
Range(Selection, Cells(5, 1)).Select
Range(Selection, Cells(5, 104)).Select
Selection.Copy
Sheets("EAC Detail - Ops EAC (2)").Select
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set wksData = Worksheets("EAC Detail - Ops EAC (2)")
'creates separate tab for pivot data and includes pertinent Acctg Adj data
Sheets("EAC Detail - Ops EAC (2)").Select
Range("A1048576").Select
Selection.End(xlUp).Select
Range(Selection, Cells(4, 1)).Select
Range(Selection, Cells(4, 104)).Select
Set rngData = Application.Selection
'selects pivottable data
Sheets.Add Before:=Worksheets("EAC Information -->")
ActiveSheet.Name = "Analysis Table"
Sheets("Analysis Table").Select
With ActiveWorkbook.Sheets("Analysis Table").Tab
.Color = 5287936
.TintAndShade = 0
End With
Set wksDest = Worksheets("Analysis Table")
'creates tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "Current EAC"
Range("B1").Select
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
'EAC label
Set pvtTable = wksData.PivotTableWizard(SourceType:=xlDatabase, _
SourceData:=rngData, TableDestination:=wksDest.Range("B5"), TableName:="EACPivot")
'creates pivottable
With pvtTable
With .PivotFields("GAAP Cntrct")
.Orientation = xlRowField
.Position = 1
.LayoutBlankLine = False
End With
With .PivotFields("GAAP Cntrct Consideration")
.Orientation = xlRowField
.Position = 2
.LayoutBlankLine = False
End With
With .PivotFields("CLIN")
.Orientation = xlRowField
.Position = 3
.LayoutBlankLine = False
End With
With .PivotFields("PSR PAG")
.Orientation = xlRowField
.Position = 4
.LayoutBlankLine = False
End With
With .PivotFields("ITD Actuals")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = " $* #,##0.00_);_($* (#,##0.00);_($* -??_);_(@_)"
.Name = "ITD "
End With
DataFldCount = DataFldCount + 1
With .PivotFields("ETC")
.Orientation = xlDataField
.Position = 2
.NumberFormat = " $* #,##0.00_);_($* (#,##0.00);_($* -??_);_(@_)"
.Name = "ETC "
End With
With .PivotFields("EAC")
.Orientation = xlDataField
.Position = 3
.NumberFormat = " $* #,##0.00_);_($* (#,##0.00);_($* -??_);_(@_)"
.Name = "EAC "
End With
'creates pivot table layout
With ActiveSheet.PivotTables("EACPivot").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
'moves data to columns
End With
'Else
pvtTable.RefreshTable
'End If
On Error Resume Next
For Each pvtTable In Application.ActiveSheet.PivotTables
For Each pvtFld In pvtTable.PivotFields
pvtFld.Subtotals(1) = True
pvtFld.Subtotals(1) = False
Next
Next
'removes subtotals
With ActiveSheet.PivotTables("EACPivot")
.ColumnGrand = False
.RowGrand = False
End With
'removes grand total
ActiveSheet.PivotTables("EACPivot").RowAxisLayout xlCompactRow
'change pivottable layout to compact
ActiveSheet.PivotTables("EACPivot").TableStyle2 = "PivotStyleDark23"
'change pivottable style
wksDest.UsedRange.Columns.AutoFit
'resizes column widths
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
I am new to VBA so any help would be greatly appreciated!!!
Sub PivotTable()
''''''''''''''''Current EAC Pivot Table''''''''''''''''''''
Dim wksData As Worksheet
Dim wksDest As Worksheet
Dim rngData As Range
Dim pvtTable As PivotTable
Dim DataFldCount As Long
Dim pvtFld As PivotField
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim EAC_Model As Workbook
Dim EACModel As Variant
Dim EAC_Element As Integer
EACModel = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*", Title:="Please select your EAC Model")
Workbooks.Open (EACModel)
Set EAC_Model = ActiveWorkbook
'Opens EAC File
On Error Resume Next
Worksheets("EAC Detail - Ops EAC (2)").Delete
On Error GoTo 0
On Error Resume Next
Worksheets("Analysis Table").Delete
On Error GoTo 0
'Removes prior analysis
Sheets("EAC Detail - Ops EAC").Select
Sheets("EAC Detail - Ops EAC").Copy Before:=Sheets("EAC Process Information -->")
Columns("N:AA").Select
Selection.EntireColumn.Hidden = False
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("EAC Detail - Acctg Adj").Select
Columns("N:AA").Select
Selection.EntireColumn.Hidden = False
Rows("4:4").Select
Selection.AutoFilter
Range("M8").Select
ActiveSheet.Range("$A$4:$CZ$49").AutoFilter Field:=13, Criteria1:= _
"=Contract Loss Exp", Operator:=xlOr, Criteria2:="=Revenue Adjustment"
Range("A1048576").Select
Selection.End(xlUp).Select
Range(Selection, Cells(5, 1)).Select
Range(Selection, Cells(5, 104)).Select
Selection.Copy
Sheets("EAC Detail - Ops EAC (2)").Select
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set wksData = Worksheets("EAC Detail - Ops EAC (2)")
'creates separate tab for pivot data and includes pertinent Acctg Adj data
Sheets("EAC Detail - Ops EAC (2)").Select
Range("A1048576").Select
Selection.End(xlUp).Select
Range(Selection, Cells(4, 1)).Select
Range(Selection, Cells(4, 104)).Select
Set rngData = Application.Selection
'selects pivottable data
Sheets.Add Before:=Worksheets("EAC Information -->")
ActiveSheet.Name = "Analysis Table"
Sheets("Analysis Table").Select
With ActiveWorkbook.Sheets("Analysis Table").Tab
.Color = 5287936
.TintAndShade = 0
End With
Set wksDest = Worksheets("Analysis Table")
'creates tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "Current EAC"
Range("B1").Select
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
'EAC label
Set pvtTable = wksData.PivotTableWizard(SourceType:=xlDatabase, _
SourceData:=rngData, TableDestination:=wksDest.Range("B5"), TableName:="EACPivot")
'creates pivottable
With pvtTable
With .PivotFields("GAAP Cntrct")
.Orientation = xlRowField
.Position = 1
.LayoutBlankLine = False
End With
With .PivotFields("GAAP Cntrct Consideration")
.Orientation = xlRowField
.Position = 2
.LayoutBlankLine = False
End With
With .PivotFields("CLIN")
.Orientation = xlRowField
.Position = 3
.LayoutBlankLine = False
End With
With .PivotFields("PSR PAG")
.Orientation = xlRowField
.Position = 4
.LayoutBlankLine = False
End With
With .PivotFields("ITD Actuals")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = " $* #,##0.00_);_($* (#,##0.00);_($* -??_);_(@_)"
.Name = "ITD "
End With
DataFldCount = DataFldCount + 1
With .PivotFields("ETC")
.Orientation = xlDataField
.Position = 2
.NumberFormat = " $* #,##0.00_);_($* (#,##0.00);_($* -??_);_(@_)"
.Name = "ETC "
End With
With .PivotFields("EAC")
.Orientation = xlDataField
.Position = 3
.NumberFormat = " $* #,##0.00_);_($* (#,##0.00);_($* -??_);_(@_)"
.Name = "EAC "
End With
'creates pivot table layout
With ActiveSheet.PivotTables("EACPivot").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
'moves data to columns
End With
'Else
pvtTable.RefreshTable
'End If
On Error Resume Next
For Each pvtTable In Application.ActiveSheet.PivotTables
For Each pvtFld In pvtTable.PivotFields
pvtFld.Subtotals(1) = True
pvtFld.Subtotals(1) = False
Next
Next
'removes subtotals
With ActiveSheet.PivotTables("EACPivot")
.ColumnGrand = False
.RowGrand = False
End With
'removes grand total
ActiveSheet.PivotTables("EACPivot").RowAxisLayout xlCompactRow
'change pivottable layout to compact
ActiveSheet.PivotTables("EACPivot").TableStyle2 = "PivotStyleDark23"
'change pivottable style
wksDest.UsedRange.Columns.AutoFit
'resizes column widths
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
