Creating a Macro in VBA to make pivot table from selected data

Question

I am new to VBA and I am trying to create a macro to which I will make a button out of to make a Pivot Table from data that I paste into a certain sheet.

The code below is from a macro that I tried to produce step-by-step of how I want the code to run.

I am selecting certain data (Columns A:G), then pasting that data into another sheet and then creating a blank table.

My code:

Sub Macro2()
'
' Macro2 Macro
'

'
    Cells.Select
    Range("A672198").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet2!R1C1:R1048576C7", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Sheet11!R3C1", TableName:="PivotTable6", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Sheet11").Select
    Cells(3, 1).Select
End Sub

The issue comes from:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Sheet2!R1C1:R1048576C7", Version:=xlPivotTableVersion15).CreatePivotTable _
            TableDestination:="Sheet11!R3C1", TableName:="PivotTable6", DefaultVersion _
            :=xlPivotTableVersion15

with an error message of:

Run-time error '5': invalid procedure call or argument

I have tried researching how to make a basic macro to create a blank pivot table of my data but I can't seem to figure it out.

I have also looked at many posts from this site and nothing really helped. I tried referencing this post but no luck.

I am using Microsoft Excel 2013*

Great first attempt Alex, I think we can help you from here - unfortunately recording macros with pivot table creation can be a little tricky. To start, which sheet are you grabbing columns A:G from, and which sheet are you trying to paste that data on? Do you always need to add a new sheet? Does it matter what the sheet name is? — dwirony, Apr 12 '18 at 12:55
@dwirony thank you for the support! So I am always going to be grabbing data from Columns A:G but the row length of A:G will be changing depending on the data. Also, I am always pasting the data in sheet 2 and will be wanting to create a pivot table in sheet 3. If having a new sheet is too much I am ok with the Pivot Table being in the same sheet as the original data (sheet 2). The name does not matter at this point in time. Thanks! — alex_fields1, Apr 12 '18 at 12:58

score 0 · Answer 1 · answered Apr 12 '18 at 13:13

Give this a shot - this will take columns A:G from Sheet1 and create a new sheet (named whatever), paste all the data into columns A:G on that sheet, then create a third sheet and create a new pivot table based on the data from sheet 2.

Option Explicit
Sub Test()

Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
Dim lastrow As Long

Set sht1 = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht1.Cells(sht1.Rows.Count, "A").End(xlUp).Row

Set sht2 = Sheets.Add(After:=Sheets(Sheets.Count))
Set sht3 = Sheets.Add(After:=Sheets(Sheets.Count))

sht2.Range("A1:G" & lastrow).Value = _
sht1.Range("A1:G" & lastrow).Value

ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        sht2.Name & "!R1C1:R" & lastrow & "C7", Version:=6).CreatePivotTable TableDestination:= _
        sht3.Name & "!R3C1", DefaultVersion:=6

End Sub

score 0 · Answer 2 · answered Apr 12 '18 at 13:16

Try this:

Sub create_pivot()

Dim mysourcedata, mydestination As String
Dim lr As Long

lr = Sheets("Sheet1").Range("A1").End(xlDown).Row '' find your last row with data

mysourcedata = "Sheet1!R1C1:R" & lr & "C5"
mydestination = "Sheet2!R1C1"

    Sheets("Sheet2").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, sourcedata:= _
        mysourcedata, Version:=6).CreatePivotTable TableDestination:= _
        mydestination, TableName:="PivotTable1", DefaultVersion:=6

End Sub

Creating a Macro in VBA to make pivot table from selected data

2 Answers2