recorded macro gives runtime error 5

Question

I'm new with VBA. I recorded a macro an d wanted to edit it. after recording it, I wanted to run it one time. But when I did, it returned the Runtime Error 5.

The Macro should take and from a sheet and add it into a pivottable in another sheet.

So this is the code, where the error is based.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "sourcetable!R1C1:R1048576C21", Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:="Tabelle2!R3C1", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion14

thanks for your help

Does the sheet `Tabelle2` have any other data other than the pivot? — Siddharth Rout, Sep 20 '13 at 10:11
no it doesn't it's a new sheet only for this pivot @SiddharthRout — JUNGE, Sep 20 '13 at 10:12

score 1 · Accepted Answer · answered Sep 20 '13 at 10:17

no it doesn't it's a new sheet only for this pivot @SiddharthRout – beginner 4 mins ago

The simplest way to do is

With ActiveWorkbook
    .Sheets("Tabelle2").Cells.Clear

    .PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "sourcetable!R1C1:R1048576C21", Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:="Tabelle2!R3C1", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion14
End With

Also I noticed that you have source data defined till the row 1048576. Why so? A more perfect way would be to find the last row and then construct your range. For example

Sub Sample()
    Dim lRow As Long

    With ActiveWorkbook
        '~~> Find last row in sheet sourcetable
        With .Sheets("sourcetable")
            If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
                lRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
            Else
                lRow = 1
            End If
        End With

        .Sheets("Tabelle2").Cells.Clear

        .PivotCaches.Create(SourceType:=xlDatabase, _
                            SourceData:="sourcetable!R1C1:R" & _
                                                        lRow & _
                                                        "C21", _
                            Version:=xlPivotTableVersion14).CreatePivotTable _
                            TableDestination:="Tabelle2!R3C1", _
                            TableName:="PivotTable1", _
                            DefaultVersion:=xlPivotTableVersion14
    End With
End Sub

@LS_dev: If you are constantly creating Pivot1 in that sheet then it won't :) — Siddharth Rout, Sep 20 '13 at 10:25
WOW it works! Thank you very much! But I have one more question: What makes "If Application.WorksheetFunction.CountA(.Cells) <> 0 Then"? — JUNGE, Sep 20 '13 at 11:14

LS_ᴅᴇᴠ · Answer 2 · 2013-09-20T10:56:01.380

0

First: does sourcetable and Tabelle2 worksheets exist?

Then, try it removing all optional meaningless arguments:

ActiveWorkbook.PivotCaches.Create(xlDatabase, "sourcetable!R1C1:R1048576C21") _
    .CreatePivotTable ActiveWorkbook.Worksheets("Tabelle2").Range("R3C1")

edited Sep 20 '13 at 10:56

answered Sep 20 '13 at 10:13

LS_ᴅᴇᴠ

10,823
1
23
46

yes they exist. i tried your solution, but at the moment I have now an error 438 :S – JUNGE Sep 20 '13 at 10:21

recorded macro gives runtime error 5

2 Answers2