Macro I recorded won't run

[MarvinH]

Hi I'm a complete beginner with macros, I have 8 excel report I do every week that use the same format and tabs, I want to automate this to save me some time, I have recorded a macro in spreadsheet but I keep getting run time error 1004, can you help me understand where I am going wrong please?

 

[mrshl9898]

Paste the code in here so we can see.

When it errors click Debug, it'll highlight a line of code, show us which line too.

 

[MarvinH]

Hi thanks for the reply i get this error on my screen in yellow

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=6).CreatePivotTable TableDestination:="Sheet3!R3C1", _
TableName:="PivotTable2", DefaultVersion:=6

 

[MarvinH]

Paste the code in here so we can see.

When it errors click Debug, it'll highlight a line of code, show us which line too.

Hi thanks for the reply i get this error on my screen in yellow

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=6).CreatePivotTable TableDestination:="Sheet3!R3C1", _
TableName:="PivotTable2", DefaultVersion:=6

 

[mrshl9898]

Thanks, You're adding a sheet that is generating a name then trying to add a Pivot to Sheet3. Is this what you want? Does Sheet3 exist?

 

[mrshl9898]

1004 is usually happens when VBA code refers to a cell or range that doesn’t exist

 

[mrshl9898]

Maybe try this

Sub AddPivot()

Dim ws As Worksheet

Sheets.Add.Name = "NewSheet"
Set ws = Sheets("NewSheet")

    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1" , Version:=6).CreatePivotTable TableDestination:= _
        ws.Range("A3"), TableName:="PivotTable2", DefaultVersion:=6

End Sub

 

[jasonb75]

Using code to create a pivot table is not something that I've tried doing so I may be misreading the code, but it looks like you're trying to create an object that already exists.

 

[MarvinH]

Hi all, yeah sheet 3 does not exist but I have run into this problem following your code

Sub Macro2()

'

' Macro2 Macro

'



'





Dim ws As Worksheet



Sheets.Add.Name = "Newsheet"

Set ws = Sheets("Newsheet")



Application.CutCopyMode = False

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

"Table1", Version:=6).CreatePivotTable TableDestination:= _ ws.Range("A3"),

TableName:="PivotTable3", DefaultVersion:=6

Sheets("Sheet4").Select

 

[mrshl9898]

Code I pasted runs fine for me. Is it 1004 again on Sheets("Sheet4").Select? Does it exist?