Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Creating Pivot Tables with macro Problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 205
Default Creating Pivot Tables with macro Problem
Hi All,

I create files from results of a query in an application...so the data range
changes constantly based on the items in the query...anyway...to hopefully do
all the formulas and pivot table creation in a macro that can be used with
each new query.

All works fine except...the 1st pivot table created will not include the row
fields...all other pivots in the same macro are created just fine..even using
some of the same row fields.

Below is the code I am using to start...the array for RowFields are the
hang-up. The fields are correct as entered...including the few with an
underscore in the titles.

I am totally stumped as what to try and any help would be greatly appreciated!

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("BU
Code", _
"Vendor Alpha", "VENDOR_NAME", "FY", "BPO_NUMBER")
With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With

Tis is the portion of the 2nd pivot table created in the macro and works
perfect...including the rows added to the pivot for and array.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:=Range _
("H3"), TableName:="PivotTable2"
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("BU
Code", _
"FY", "Rec'd or Not")
With
ActiveSheet.PivotTables("PivotTable2").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With
--
Linda

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Creating Pivot Tables with macro Problem
Can you create the table manually with those as row fields. there is a
possibility that your row fields cause the pivot table to exceed allowable
limits.

http://support.microsoft.com/?kbid=157486
XL97: Limits of PivotTables in Microsoft Excel 97 (Q157486)

http://support.microsoft.com/?kbid=211517
XL2000: Limits of PivotTables in Microsoft Excel 2000 (Q211517)

http://support.microsoft.com/?kbid=291061
XL2002: Limits of PivotTables in Microsoft Excel 2002 (Q291061)


http://support.microsoft.com/default.aspx?id=820742
Description of the limits of PivotTable reports in Excel
(2003, 2002 Standard Edition)


http://support.microsoft.com/?kbid=264626
XL2000: Microsoft Excel 2000 Specifications (Q264626)
--
Regards,
Tom Ogilvy

"Linda" wrote in message
...
Hi All,

I create files from results of a query in an application...so the data
range
changes constantly based on the items in the query...anyway...to hopefully
do
all the formulas and pivot table creation in a macro that can be used with
each new query.

All works fine except...the 1st pivot table created will not include the
row
fields...all other pivots in the same macro are created just fine..even
using
some of the same row fields.

Below is the code I am using to start...the array for RowFields are the
hang-up. The fields are correct as entered...including the few with an
underscore in the titles.

I am totally stumped as what to try and any help would be greatly
appreciated!

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("BU
Code", _
"Vendor Alpha", "VENDOR_NAME", "FY", "BPO_NUMBER")
With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With

Tis is the portion of the 2nd pivot table created in the macro and works
perfect...including the rows added to the pivot for and array.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:=Range _
("H3"), TableName:="PivotTable2"
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("BU
Code", _
"FY", "Rec'd or Not")
With
ActiveSheet.PivotTables("PivotTable2").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With
--
Linda



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Creating Pivot Tables with macro Problem
Can you pick out a column that always has something in it (I'll use A). And a
row that always has something in it (I'll use 1).

Dim LastCol As Long
Dim LastRow As Long
Dim myRng As Range

With Worksheets("current oblig")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("a1", .Cells(LastRow, LastCol))
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=myRng.Address(external:=True)).CreateP ivotTable _
TableDestination:="", TableName:="PivotTable1"

.....

Another option would be to give that range a nice dynamic name that would adjust
when the range changed size.

See Debra Dalgleish's site for some tips:
http://contextures.com/xlNames01.html#Dynamic

Linda wrote:

Hi All,

I create files from results of a query in an application...so the data range
changes constantly based on the items in the query...anyway...to hopefully do
all the formulas and pivot table creation in a macro that can be used with
each new query.

All works fine except...the 1st pivot table created will not include the row
fields...all other pivots in the same macro are created just fine..even using
some of the same row fields.

Below is the code I am using to start...the array for RowFields are the
hang-up. The fields are correct as entered...including the few with an
underscore in the titles.

I am totally stumped as what to try and any help would be greatly appreciated!

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("BU
Code", _
"Vendor Alpha", "VENDOR_NAME", "FY", "BPO_NUMBER")
With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With

Tis is the portion of the 2nd pivot table created in the macro and works
perfect...including the rows added to the pivot for and array.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:=Range _
("H3"), TableName:="PivotTable2"
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("BU
Code", _
"FY", "Rec'd or Not")
With
ActiveSheet.PivotTables("PivotTable2").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With
--
Linda

--

Dave Peterson
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 205
Default Creating Pivot Tables with macro Problem
Yes, the pivot table can be created manually and works fine.
--
Linda



"Tom Ogilvy" wrote:

Can you create the table manually with those as row fields. there is a
possibility that your row fields cause the pivot table to exceed allowable
limits.

http://support.microsoft.com/?kbid=157486
XL97: Limits of PivotTables in Microsoft Excel 97 (Q157486)

http://support.microsoft.com/?kbid=211517
XL2000: Limits of PivotTables in Microsoft Excel 2000 (Q211517)

http://support.microsoft.com/?kbid=291061
XL2002: Limits of PivotTables in Microsoft Excel 2002 (Q291061)


http://support.microsoft.com/default.aspx?id=820742
Description of the limits of PivotTable reports in Excel
(2003, 2002 Standard Edition)


http://support.microsoft.com/?kbid=264626
XL2000: Microsoft Excel 2000 Specifications (Q264626)
--
Regards,
Tom Ogilvy

"Linda" wrote in message
...
Hi All,

I create files from results of a query in an application...so the data
range
changes constantly based on the items in the query...anyway...to hopefully
do
all the formulas and pivot table creation in a macro that can be used with
each new query.

All works fine except...the 1st pivot table created will not include the
row
fields...all other pivots in the same macro are created just fine..even
using
some of the same row fields.

Below is the code I am using to start...the array for RowFields are the
hang-up. The fields are correct as entered...including the few with an
underscore in the titles.

I am totally stumped as what to try and any help would be greatly
appreciated!

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("BU
Code", _
"Vendor Alpha", "VENDOR_NAME", "FY", "BPO_NUMBER")
With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With

Tis is the portion of the 2nd pivot table created in the macro and works
perfect...including the rows added to the pivot for and array.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:=Range _
("H3"), TableName:="PivotTable2"
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("BU
Code", _
"FY", "Rec'd or Not")
With
ActiveSheet.PivotTables("PivotTable2").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With
--
Linda




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Creating Pivot Tables with macro Problem
I misread missing row fields for missing rows.

Never mind my post.

Dave Peterson wrote:

Can you pick out a column that always has something in it (I'll use A). And a
row that always has something in it (I'll use 1).

Dim LastCol As Long
Dim LastRow As Long
Dim myRng As Range

With Worksheets("current oblig")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("a1", .Cells(LastRow, LastCol))
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=myRng.Address(external:=True)).CreateP ivotTable _
TableDestination:="", TableName:="PivotTable1"

....

Another option would be to give that range a nice dynamic name that would adjust
when the range changed size.

See Debra Dalgleish's site for some tips:
http://contextures.com/xlNames01.html#Dynamic

Linda wrote:

Hi All,

I create files from results of a query in an application...so the data range
changes constantly based on the items in the query...anyway...to hopefully do
all the formulas and pivot table creation in a macro that can be used with
each new query.

All works fine except...the 1st pivot table created will not include the row
fields...all other pivots in the same macro are created just fine..even using
some of the same row fields.

Below is the code I am using to start...the array for RowFields are the
hang-up. The fields are correct as entered...including the few with an
underscore in the titles.

I am totally stumped as what to try and any help would be greatly appreciated!

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("BU
Code", _
"Vendor Alpha", "VENDOR_NAME", "FY", "BPO_NUMBER")
With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With

Tis is the portion of the 2nd pivot table created in the macro and works
perfect...including the rows added to the pivot for and array.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Current Oblig'!R1C1:R5932C36").CreatePivotTable
TableDestination:=Range _
("H3"), TableName:="PivotTable2"
ActiveSheet.PivotTables("PivotTable2").SmallGrid = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("BU
Code", _
"FY", "Rec'd or Not")
With
ActiveSheet.PivotTables("PivotTable2").PivotFields ("OBLIGATION_AMOUNT")
.Orientation = xlDataField
.Caption = "Sum of OBLIGATION_AMOUNT"
.Function = xlSum
End With
--
Linda

--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating Pivot Tables with Macros Problem Linda Excel Programming 1 November 2nd 05 05:36 PM
Problem with a recorded macro to create two pivot tables from same data rjamison Excel Programming 0 June 14th 05 12:14 AM
Problem with a recorded macro to create two pivot tables from same data Pete Straman via OfficeKB.com[_2_] Excel Programming 1 April 20th 05 05:28 PM
Creating Pivot Tables inside a Macro farrell77 Excel Programming 4 February 18th 05 09:54 PM
problem with macro for pivot tables Hans Excel Programming 0 July 8th 04 01:50 PM


All times are GMT +1. The time now is 02:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement