Option Explicit
Sub Getdata_fromsheets()
Dim sh As Object, Data As Range, T As Double
T = Timer
Application.ScreenUpdating = False
Sheets("Temp").Range("3:65000").ClearContents
Sheets("PVT").Range("3:65000").ClearContents
For Each sh In ThisWorkbook.Sheets
If sh.name <> "PVT" Then
sh.Range("A4").CurrentRegion.Offset(1).copy
Sheets("Temp").Range("A65536").End(3).Offset(1).PasteSpecial (12)
Application.CutCopyMode = False
End If
Next
Call PIVOT_ADD
Application.ScreenUpdating = True
Sheets("PVT").Range("A1") = Timer - T
End Sub
Sub PIVOT_ADD()
Dim PVT As PivotTable, PVT_Data As Range
Set PVT_Data = Sheets("Temp").Range("A2").CurrentRegion
ActiveWorkbook.PivotCaches.Create(xlDatabase, PVT_Data).CreatePivotTable (Sheets("PVT").Range("C12")), "Pivot"
Set PVT = ActiveSheet.PivotTables("Pivot")
With PVT
With .PivotFields("S")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Paid Date")
.Orientation = xlPageField
.Position = 1
End With
.AddDataField PVT.PivotFields("OUT"), ".OUT", xlSum
.PivotFields(".OUT").NumberFormat = "#,##0"
.TableStyle2 = "OPTION1"
.RowAxisLayout xlTabularRow
End With
ActiveWorkbook.RefreshAll
End Sub