VBA macro to create pivot table on sheet2 with data source on sheet1

[Josu]

I apologize, tried to write shortest possible header

Basically I have sheet calls Capped where I got my sql query exported (Columns A:K)
Based on that sheet Data I want to create a pivot table
I found one macro, but it doesnt work by some reason
From sheet Data I need 3 columns.
Customer = as row
Type = as column
Amount = as units

P.S. Found my issue, this due to incorrect range In sheet data I need to have cell A1 blank, how I need to edit my coordinates?

Sub InsertPivotTable()

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Total Capped Volume").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Total Capped"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Total Capped")
Set DSheet = Worksheets("Data")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="Total Capped")

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="Total Capped")

'Insert Row Fields
With ActiveSheet.PivotTables("Total Capped").PivotFields("Customer")
.Orientation = xlRowField
.Position = 1
End With


'Insert Column Fields
With ActiveSheet.PivotTables("Total Capped").PivotFields("Type")
.Orientation = xlColumnField
.Position = 1
End With

'Insert Data Field
With ActiveSheet.PivotTables("Total Capped").PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Amount "

Thank you in advance

 

[Skyybot]

Can you write in

Sheets("Sheet_Name").Range("A1").ClearContents

 

[Josu]

Can you write in

Sheets("Sheet_Name").Range("A1").ClearContents

Probably I explained incorrectly
I want to create pivot table which range starts in A1, but my macro doesn't work as A1 is blank cell (A2 and other cells under are occupied). Basically If i delete column A - everything works

 

[Skyybot]

This clears the first Column.

Sheets("Sheet_Name").Columns(1).ClearContents

 

[Josu]

This clears the first Column.

Sheets("Sheet_Name").Columns(1).ClearContents

I don't need to clear anything Just to create pivot table

 

[Skyybot]

..

 

[Skyybot]

Sorry, I thought you said that if you deleted the first column everything works fine. Let me think on this one.

 

[SpillerBD]

Change your PRange value.
I would use the CurrentRange starting from (3,3) or C3.

 

[Josu]

Change your PRange value.
I would use the CurrentRange starting from (3,3) or C3.

Not really working, when I change

Set PRange = DSheet.Cells(3, 3).Resize(LastRow, LastCol)

nothing happening

 

[SpillerBD]

And I don't think you need to Set this value. Try

PRange = DSheet.Range("C3").CurrentRegion