Excel VBA从表创建数据透视表

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.

所以我是VBA的新手，并试图在飞行中学习一些工作项目，这让我疯狂。

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:

我有一个宏来清理我的数据并将其放入表中然后我正在处理一个宏来获取该表并将其转换为数据透视表/图表。第一个宏在电子表格“DailyData”上创建Table1。供参考，这里是：

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.

理想情况下，我的Pivot将“部件号”作为行字段，然后将“设置时间”“间接人工”“工时”“标准工时”“劳动效率”作为值字段。

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:

到目前为止，我甚至没有能够获得其中一个值字段以显示权利，更不用说所有5.所以我当前尝试获得第一个值字段显示正确如下：

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

谢谢，泰勒

2 个解决方案

#1

try this replacing

试试这个替换

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

with this

有了这个

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

#2

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

尝试下面的代码，我已经对这两个函数进行了修改，以更好地利用Excel的VBA功能：

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

#1