Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 14th, 2011, 11:28 AM
Registered User
 
Join Date: Nov 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
 
Old November 14th, 2011, 01:42 PM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Welcome to the board and to VBA! Please use [ code ] tags when posting VBA code.

In order to use "hanging dot notation" you need to use a With block first. i.e.

Code:
With Application
.ScreenUpdating = False
.PrintCommunication = False
End With
But on the very next line, you use dots without code that specifies the object:

Code:
finalRow = .Cells(.Rows.Count, 1).End(xlUp).Row
It should be something like

Code:
 
With ActiveSheet
  finalRow = .Cells(.Rows.Count, 1).End(xlUp).Row
  finalCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
__________________
Regards,
JP
JP SoftTech
 
Old November 18th, 2011, 02:13 PM
Registered User
 
Join Date: Nov 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks!

Got it :)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter Pivot Table(s) based on Cell Value outside Pivot steplawn Beginning VB 6 0 May 11th, 2010 09:01 PM
VBA for Pivot Tables stealthdevil Excel VBA 3 March 21st, 2007 04:01 PM
How to replace pivot table data fields using VBA? rstober Excel VBA 2 August 19th, 2005 06:06 AM
Help Needed to write vba for Pivot Table in Excel sunny76 Excel VBA 1 June 28th, 2005 01:44 AM
insert 2-D pivot table into a regular recordset ? cs001 SQL Server 2000 1 November 17th, 2003 08:16 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.