Range Reference Issue

[baycolor]

The code below produces a compile error in the VBA macro builder window. The "red" error line is line 9 (starts with "ActiveWorkbook.PivotCaches...". I'm about 99% sure the problem is with my SourceData value and the range I'm attempting to set this to. Can anyone see anything wrong with this. I'm using the same kind of reference in other places in my VBA code.

Thanks

Sub bldpvt()
    Dim iLastRow As Integer
    Dim iFirstRow As Integer
    Worksheets("AllIndustries").Activate
    ActiveCell.SpecialCells(xlLastCell).Select
    iLastRow = ActiveCell.Row
    iFirstRow = 1
    Worksheets("Sheet2").Activate
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Sheets("AllIndustries").Range("A" & iFirstRow, "N" & iLastRow).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
        "Official Customer ", "Industry"), ColumnFields:="Tier"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Account").Orientation = _
        xlDataField
    Application.CommandBars("PivotTable").Visible = False
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("A2:G31").Select
    Range("G31").Activate
    Selection.Copy
    Range("I2").Select
    ActiveSheet.Paste
    Range("L7").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
        "Official Customer ", "Region"), ColumnFields:="Tier"
    ActiveWorkbook.ShowPivotTableFieldList = True
    Application.CommandBars("PivotTable").Visible = False
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

 

[Bong]

.Range("A" & iFirstRow, "N" & iLastRow)

shouldn't that be:
.Range("A" & iFirstRow [red]& ":[/red]N" & iLastRow)

_________________
Bob Rashkin

 

[baycolor]

That didn't seem to work. The entire line goes red again. I even changed it to:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Sheets("AllIndustries").Range("A1:N12").CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

and it didn't work. Not sure what's going on. I'm wondering if this problem has anything to do with the fact that this is in a pivot table statement.

 

[baycolor]

NOTE: The following does work but it doesn't solve the problem as I want to dynamically set the SourceData value:

'    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
'        "AllIndustries!R1C1:R12923C14").CreatePivotTable TableDestination:="", _
'        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

 

[SkipVought]

Your problem may be the TableName. You could have pivot table objects in the sheet that you do not know about.

If this pivot table should be the ONLY one on the sheet, then run this procedure to remove ALL pivot tables before adding a new one...

dim pvt as pivottable
for each pvt in activesheet.pivottables
  pvt.delete
next

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[ck1999]

Have you tried sticking with the RC type of referencing

  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
       "allindustries!R1C1:R" & iLastRow & "C14").CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

skip, I could not get pvt.delete to work for me (excel 03)

ck1999

 

[SkipVought]

ck,

You're absolutely correct. I was mistakenly thinking that deleting PivotTables was similar to deleting QueryTables.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[baycolor]

I did stick with the RC referencing like you showed and it worked. Having other pivot table issues now that I may have to start another thread about but this at least working.

Thanks