Pivot Table error

[AKK]

Getting stuck on a line of code that deals with pivot tables -

The original code from the macro I recorded is below. When I tried running it, I got a message that said "unable to get the pivot tables property of the worksheet class." The line where it got stuck is in bold.

Sub SweetTable2()

Sheets.Add
ActiveSheet.Name = "Pivot Table"
Sheets.Add
ActiveSheet.Name = "Al's Table"

Sheets("Sheet1").Select
Range("E1:G40").Select
Range("G40").Activate
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C5:R40C7").CreatePivotTable TableDestination:= _
"'[test.xls]Pivot Table'!R1C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Meeting Name")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Meeting Name"), "Count of Meeting Name", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlColumnField
.Position = 1
End With
Range("A2:I8").Select
Range("A8").Activate
Selection.Copy
Sheets("Al's Table").Select
ActiveSheet.Paste
Range("A9").Select

End Sub

I did some searching about this error message, and it seemed like most of the time when it happened, it meant that the sheet with the PT in it wasn't the active sheet. So I added the bolded line below to the code:

Sub SweetTable2()

Sheets.Add
ActiveSheet.Name = "Pivot Table"
Sheets.Add
ActiveSheet.Name = "Al's Table"
Sheets("Sheet1").Select
Range("E1:G40").Select
Range("G40").Activate
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C5:R40C7").CreatePivotTable TableDestination:= _
"'[test.xls]Pivot Table'!R1C1", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion10
Sheets("Pivot Table").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Meeting Name")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Meeting Name"), "Count of Meeting Name", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlColumnField
.Position = 1
End With
Range("A2:I8").Select
Range("A8").Activate
Selection.Copy
Sheets("Al's Table").Select
ActiveSheet.Paste
Range("A9").Select
End Sub

When I ran THAT code, it didn't get stuck anymore, but only built one row of the pivot table. (I also checked that the names of the table & all the fields were correct.) Can anyone help?

Thanks!

[anandbohra]

pl provide sample data to test check
& use vba tags to wrap vba codes in your thread