VBA Code for creating a pivot table

[morz68]

Hi

I have tried recording, and looking through google and editing code. But for the life of me I can't get my button to work.

I just want to:

1. Select current range
2. Create Pivot Table on a new sheet
3. Put one field in row
4. Put one field in column

I thought recording would be easy, but it doesn't work.

 

[mse330]

Hello morz68, try the below code ... Note that you have to select the range of data first before running the code

Sub Create_Pivot()

Dim Rg As Range
Set Rg = Selection

Sheets.Add After:=Sheets(Sheets.Count)

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Rg).CreatePivotTable _
    TableDestination:=Sheets(Sheets.Count).Name & "!R3C1", TableName:="PVT1"

With ActiveSheet.PivotTables("PVT1")
    .PivotFields(1).Orientation = xlColumnField
    .PivotFields(2).Orientation = xlRowField
End With

End Sub

 

[morz68]

That's fantastic, I did get a solution with recording (not sure why it didn't work at first?)

Can you suggest any way to have the data selected before. I'm currently, Ctrl+Shift+Down Arrow and Right Arrow?

Thanks again
M

 

[mse330]

Perhaps changing the range defining to this

Set Rg = ActiveSheet.UsedRange