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