Trying to copy every row from the pivot table and paste it 12 times into the table. I want each 12 rows to be consective. The For x= 1 to 12 isn't working... Any suggestions??
VBA Code:
Sub Update_Dynamics_Upload_Click()
Application.ScreenUpdating = False
Dim tbl As ListObject
Set tbl = Worksheets("Dynamics Budget Upload").ListObjects("DynamicsBudgetUpload")
Dim GolfPT As PivotTable, MaintPT As PivotTable, FBPT As PivotTable, GAPT As PivotTable
Set GolfPT = Worksheets("Golf Pivot").PivotTables("Golf Pivot")
Set MaintPT = Worksheets("Maintenance Pivot").PivotTables("Maintenance Pivot")
Set FBPT = Worksheets("F&B Pivot").PivotTables("F&B Pivot")
Set GAPT = Worksheets("G&A Pivot").PivotTables("G&A Pivot")
GolfPT.RowAxisLayout xlTabularRow
MaintPT.RowAxisLayout xlTabularRow
FBPT.RowAxisLayout xlTabularRow
GAPT.RowAxisLayout xlTabularRow
Dim pi As PivotItem
Dim GFPTPF1 As PivotField, MTPTPF1 As PivotField, FBPTPF1 As PivotField, GAPTPF1 As PivotField
Dim GFPTPF2 As PivotField, MTPTPF2 As PivotField, FBPTPF2 As PivotField, GAPTPF2 As PivotField
Dim GFPTPF3 As PivotField, MTPTPF3 As PivotField, FBPTPF3 As PivotField, GAPTPF3 As PivotField
Set GFPTPF1 = GolfPT.PivotFields("Accounting Structure")
Set GFPTPF2 = GolfPT.PivotFields("Dimension Values")
Set GFPTPF3 = GolfPT.PivotFields("Amount Type")
Set MTPTPF1 = MaintPT.PivotFields("Accounting Structure")
Set FBPTPF1 = FBPT.PivotFields("Accounting Structure")
Set GAPTPF1 = GAPT.PivotFields("Accounting Structure")
Dim rngList As Range
Set rngList = Worksheets("Golf Pivot").Range("D5:O5")
Dim GolfPTCT As Long, MaintPTCT As Long, FBPTCT As Long, GAPTCT As Long, tblCT As Long
Dim x As Integer
GolfPTCT = GolfPT.TableRange1.Rows.Count
MaintPTCT = MaintPT.TableRange1.Rows.Count
FBPTCT = FBPT.TableRange1.Rows.Count
GAPTCT = GAPT.TableRange1.Rows.Count
tblCT = tbl.DataBodyRange.Rows.Count
ActiveWorkbook.RefreshAll
With tbl
rngList.Copy
tbl.ListColumns("Date").DataBodyRange.PasteSpecial Paste:=xlPasteValues, Transpose:=True
If GolfPTCT > 1 Then
For Each pi In GFPTPF1.PivotItems
For x = 1 To 12
GFPTPF1.DataRange.Rows.Copy
tbl.ListColumns("Accounting structure").DataBodyRange.PasteSpecial Paste:=xlPasteValues
GFPTPF2.DataRange.Rows.Copy
tbl.ListColumns("Dimension values").DataBodyRange.PasteSpecial Paste:=xlPasteValues
GFPTPF3.DataRange.Rows.Copy
tbl.ListColumns("Amount type").DataBodyRange.PasteSpecial Paste:=xlPasteValues
Next x
Next pi
End If
End With
MsgBox "Complete"
Application.ScreenUpdating = True
End Sub