Excel VBA to Create Pivot Table from Table

Question

So i am new to VBA and trying to learn on the fly for something that has come up on a work project and it is driving me nuts.

I have a macro to clean up my data and put it into a table and then I am working on a macro to take that table and turn it into a Pivot Table/Charts. The first macro creates Table1 on spreadsheet "DailyData". For reference, here it is:

Sub Format_Table()
Dim target As Worksheet
Set target = ThisWorkbook.Worksheets(1)
target.Name = "DailyData"
Worksheets("DailyData").Range("AB:AB,O:P,C:K,A:A").EntireColumn.Delete
Worksheets("DailyData").UsedRange
Worksheets("DailyData").UsedRange.Select
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
tbl.TableStyle = "TableStyleMedium15"
tbl.HeaderRowRange.Cells(1, 1).Value = "Date"
tbl.HeaderRowRange.Cells(1, 3).Value = "Machine"
Worksheets("DailyData").Cells.EntireColumn.AutoFit
End Sub

My Pivot is ideally going to have "Part Number" as a row field, and then "Setup Hours" "Indirect Labour" "Labour Hours" "Standard Labour Hours" "Labour Efficiency" as Value Fields.

So far I haven't even been able to get one of the Value field to show up right let alone all 5. So my current attempt to get the first value field to show up right is as follows:

Sub sbCreatePivot()
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Set ws = Worksheets.Add
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "DailyData!Table1")
Set pt = pc.CreatePivotTable(ws.Range("B3"))
With pt
    With .PivotFields("Part Number")
       .Orientation = xlRowField
       .Position = 1
    End With
    With .PivotFields("Labour Hours")
        .Orientation = xlColumnField
        .Position = 1
    End With
    .AddDataField .PivotFields("Labour Hours"), "Labour Hours", xlSum
End With

End Sub

I really appreciate the help as I am very new to all of this and trying my best here but I just keep hitting error after error and am running out of ways to google it.

Thanks, Tyler

Have you tried recording a macro and just modifying it to work for your liking? — Maldred
– Maldred, Commented Oct 20, 2017 at 14:46
I recorded it but when I try it on a new worksheet, aka the next day of data that would be pulled and edited by the macro, it fails. — Tyler Remy
– Tyler Remy, Commented Oct 20, 2017 at 14:56

Shai Rado · Accepted Answer · 2017-10-22 10:52:55Z

Try the code below, I have made modifications to both functions to better utilize Excel's VBA capabilties:

Sub Format_Table()

Dim targetSht As Worksheet
Dim tbl As ListObject

Set targetSht = ThisWorkbook.Worksheets(1)
targetSht.Name = "DailyData"

With targetSht
    .Range("AB:AB,O:P,C:K,A:A").EntireColumn.Delete

    Set tbl = .ListObjects.Add(SourceType:=xlSrcRange, Source:=.UsedRange, XlListObjectHasHEaders:=xlYes)
End With

With tbl
    .TableStyle = "TableStyleMedium15"
    .HeaderRowRange.Cells(1, 1).Value = "Date"
    .HeaderRowRange.Cells(1, 3).Value = "Machine"
End With

targetSht.Cells.EntireColumn.AutoFit

End Sub

Sub sbCreatePivot()

Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Dim targetSht As Worksheet
Dim tbl As ListObject

' set the target sheet
Set targetSht = ThisWorkbook.Worksheets("DailyData")
' set the Table (listObject)
Set tbl = targetSht.ListObjects("Table1")

Set ws = Worksheets.Add
' Modified line to set the Pivot Cache
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, tbl.Range.Address(False, False, xlA1, xlExternal))
Set pt = pc.CreatePivotTable(ws.Range("B3"))

With pt
    With .PivotFields("Part Number")
       .Orientation = xlRowField
       .Position = 1
    End With
    With .PivotFields("Labour Hours")
        .Orientation = xlColumnField
        .Position = 1
    End With

    ' Modified line
    .AddDataField .PivotFields("Labour Hours"), "Sum of Labour Hours", xlSum
End With

End Sub

Dman · Accepted Answer · 2017-10-20 17:34:23Z

0

try this replacing

.AddDataField .PivotFields("Labour Hours"), "Labour Hours", xlSum

with this

With .PivotFields("Labour Hours")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "0"
End With

answered Oct 20, 2017 at 17:34

Dman

1187 bronze badges

Collectives™ on Stack Overflow

Excel VBA to Create Pivot Table from Table

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related