Pivot Table VBA code: Out of Range Error

Hi Guys,

I need to automate pivot table creation through VBA, and I have hit a bit of snag that I am hoping some of you may help me out with.
The following code is used:

'
' Pivot_Table Macro
'

'
Worksheets("Summary Sheet").Activate
Worksheets("Summary Sheet").Select
Range("A5").Select
ActiveWorksheets.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R5C1:R34665C4", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Summary Sheet!R5C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14
Sheets("Summary Sheet").Select
Cells(5, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DDDD")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DDDDD1")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DDDD111")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Dialled_Number_Name"), _
"Count of Dialled_Number_Name", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DDDDD111")
.Orientation = xlColumnField
.Position = 1
End With

Now I am having the following error at the following line: Runtime error 1004 Select Method of Range Class failed.

And it occurs at the following:

Range("A5").Select

Can you guys figure out what is wrong with the code, the macro was recorded while I made the pivot table.

Are you sure that's the error line and not the next one.

Please Login or Register  to view this content.

Activeworksheets is not a valid object.
The enum xlPivotTableVersion14 would suggest you are using xl2010 not xl2007 as stated in your profile.

Did you protect the sheet whilst recording?

The line of code itself is correct, although you probably do not need all those select and activates.

Can you post example workbook, desensitize data as required, that demonstratess the error.

I assume the code is in the activeX button click event.

In which case the reference to Range and Cells select is relative to the Data sheet and not the Summary Sheet.

Much easier if you put the recorded code in a standard code module and then call that from the Click event

Sheet object

Please Login or Register  to view this content.

Standard code module

Please Login or Register  to view this content.

does it make any difference if you enclose the sheet names, that contain spaces, within single quotes? As I did in the code I posted.