Insert Pivot table VBA
Hello All,
This is my first time posting here, and I am definately very new to VBA. I'm having a problem inserting a Pivot table. The error that I get is compile error invalid or unqualified reference. It highlights the Sub line in yellow. Selects the .Row command (I highlighted it in red below) and changes the text to red at the bottom of the code that I have also highlighted.
Any and all help and suggestions very appreciated.
Here is the code:
Sub Insert_Weekly_Pivot()
'
' Insert_Weekly_Pivot Macro
' This will insert the weekly pivots, format, and set the header and footer
'
'
Dim finalRow As Long
Dim finalCol As Long
With Application
.ScreenUpdating = False
.PrintCommunication = False
End With
finalRow = .Cells(.Rows.Count, 1).End(xlUp).Row
finalCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Data!R1C1:finalRow, finalCol", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="SamplePivot", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet1").Select
ActiveSheet.PivotTables("SamplePivot").ShowDrillIn dicators = False
ActiveSheet.PivotTables("SamplePivot").RowAxisLayo ut xlTabularRow
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
With .PageSetup
.PrintArea = ""
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = "&A[]"
.RightHeader = ""
.LeftFooter = "&F[]"
.CenterFooter = "&P of &N[]"
.RightFooter = "&D &T[]"
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
ActiveSheet.PivotTables("SamplePivot").AddDataFiel d ActiveSheet.PivotTables( _
"SamplePivot").PivotFields("Amount"), "Sum of Amount", xlSum
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
.PivotFields ("SOURCING_GROUP_DESC")
.Orientation = xlRowField
.Position = 1
.AutoSort xlDescending, "Sum of Amount"
.PivotFields ("VENDOR_NAME")
.Orientation = xlRowField
.Position = 2
.AutoSort xlDescending, "Sum of Amount"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "By SGD & Vendor"
'End With - Do I need a with / end with for Active Sheet
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("By SGD & Vendor").Select
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveSheet.PivotTables("AnotherPivot").AddDataFie ld ActiveSheet.PivotTables( _
"AnotherPivot").PivotFields("Amount"), "Sum of Amount", xlSum
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
.PivotFields("SOURCING_GROUP_DESC"). _
.Orientation = xlRowField .Position = 1
.AutoSort xlDescending, "Sum of Amount"
.PivotFields ("Approver")
.Orientation = xlRowField
.Position = 2
.AutoSort xlDescending, "Sum of Amount"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "By Approver & Vendor"
End Sub
|
