Excel VBA从表中创建数据透视表

问题描述：

所以我是VBA的新手，并试图在工作项目中发现某些东西，这会让我疯狂。Excel VBA从表中创建数据透视表

我有一个宏来清理我的数据并把它放到一个表中，然后我正在处理一个宏来获取该表并将其转换为数据透视表/图表。第一个宏在电子表格“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

我的枢轴是理想将有“料号”作为行字段，然后在“设置时间”“间接劳动”，“劳动时间”，“标准的劳动时间”“劳动效率“作为价值领域。

到目前为止，我还没有能够得到的数值字段的一个露面的权利更别说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

我真的很感谢这个帮助，因为我对这一切都很陌生，并且在这里尽力而为，但是我在错误之后继续打错，并且无法通过Google的方式运行。

感谢，泰勒

您是否尝试过录制宏并修改它以符合您的喜好？ – Maldred

我记录了它，但是当我在新的工作表上尝试它时，也就是第二天将被宏拖动和编辑的数据，它将失败。 –

答

试试这个本

答

更换

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

尝试下面的代码，我做了修改，这两种功能，以更好地利用Excel的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

Excel VBA从表中创建数据透视表

相关推荐