Getting Run time Error 5 when Creating a Pivot Table

[gate68]

I am creating 10 pivot tables for data in one worksheet and putting them in a separate work sheet. i used the macro recorder but the cod eit worte stop at this line

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'RCH&BEA'!C2").CreatePivotTable TableDestination:="'RCH&BEA PIVOT TABLES'", TableName:= _
"RCH&BEA PIVOT TABLES", DefaultVersion:=xlPivotTableVersion10

The error i get is that run time error 1004 where it says this command requires atleast two rows of source data. It also says something about specifically about pivot tables but it cuts it off

 

[RoryA]

If you change this bit:
"'RCH&BEA'!C2"
to reference specific rows in column B, then it should work.

 

[gate68]

Thanks for the reply
I put B1 and B2 it is still not working i get the same error

 

[RoryA]

Assuming your data is in column B from say rows 1 to 10, try this:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'RCH&BEA'!R1C2:R10C2").CreatePivotTable TableDestination:="'RCH&BEA PIVOT TABLES'", TableName:= _
"RCH&BEA PIVOT TABLES", DefaultVersion:=xlPivotTableVersion10

 

[gate68]

Now I am getting a Run-Time Error 5 invalid procedure call or argument

 

[RoryA]

What is

'RCH&BEA PIVOT TABLES'?
A worksheet? If so, you need to specify a range on the worksheet. As a matter of interest, why are you creating a pivot table from one column of data?

 

[gate68]

Basically i have A column of dates where each is a complaint therefore i want to count how many complaints are in each day and make easy enough to pick a whole month to transfer it into minitab

 

[gate68]

It highlights the same whole line for that run-time Error 5. I dunno how to add the range to the worksheet but do i just add it to the Table Destination:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"RCH&BEA!R2C2:R10C2").CreatePivotTable TableDestination:="'RCH&BEA PIVOT TABLES'!A2", TableName:= _
"RCH&BEA PIVOT TABLES", DefaultVersion:=xlPivotTableVersion10

 

[RoryA]

Let's try breaking the line up so we can see where the problem is. Try this:

Dim pc As PivotCache, pt As PivotTable
Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R2C2:R10C2")
Set pt = pc.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("A2"), _
   TableName:="RCH&BEA PIVOT TABLES", DefaultVersion:=xlPivotTableVersion10)

 

[gate68]

Thanks alot that finally worked that section to work. Now as i went down i am getting a Run -time error 1004-Unable to get PivotFields Property of the Worksheet class. I have Named my Column Date i am not sure what has went wrong.:

With ActiveSheet.PivotTables("RCH&BEA PIVOT TABLES").PivotFields("Date")