Sub blah()
'copy sheet2 and rearrange to make suitable for a pivot table data source
Sheets("Sheet2").Copy After:=Sheets(Sheets.Count)
Set NewSht = ActiveSheet
With NewSht
lr = .Cells(Rows.Count, 1).End(xlUp).Row
.Columns("A:A").Insert
For Each cll In .Range("B2:B" & lr).Cells
If cll.Font.Italic Then cll.Offset(, -1).Value = cll.Value
Next cll
.Range("A2:A" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Range("A1").Value = "Co. Name"
.Range("A1:A" & lr).Value = Range("A1:A" & lr).Value
.Range("C1:C" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
'end of data rearrangment
'create a pivot cache for pivot table(s)
Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewSht.UsedRange.Resize(, 9)) ', Version:=6)
'create a smaller pivot table:
Set PT = pc.CreatePivotTable(TableDestination:=Sheets("Sheet3").Range("A8"))
With PT
'.ColumnGrand = False
.RowGrand = False
.RowAxisLayout xlTabularRow
.RepeatAllLabels xlRepeatLabels
.CalculatedFields.Add "The Balance", "=Debit - Credit", True
.PivotFields("The Balance").Orientation = xlDataField
With .PivotFields("Co. Name")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Sum of The Balance")
.NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
End With
End With
'create a larger pivot table:
Set PT2 = pc.CreatePivotTable(TableDestination:=Sheets("Sheet3").Range("K3"))
With PT2
.RowAxisLayout xlTabularRow
.ColumnGrand = False
.RowGrand = False
.ShowDrillIndicators = False
With .PivotFields("Co. Name")
.Orientation = xlRowField
.Subtotals(1) = True
.Subtotals(1) = False
End With
With .PivotFields("VCHDate")
.Orientation = xlRowField
.Subtotals(1) = True
.Subtotals(1) = False
End With
With .PivotFields(" Ref No.")
.Orientation = xlRowField
.Subtotals(1) = True
.Subtotals(1) = False
End With
With .PivotFields("Cheque No.")
.Orientation = xlRowField
.Subtotals(1) = True
.Subtotals(1) = False
End With
With .PivotFields("Particulars")
.Orientation = xlRowField
.Subtotals(1) = True
.Subtotals(1) = False
End With
With .PivotFields("VCHType")
.Orientation = xlRowField
.Subtotals(1) = True
.Subtotals(1) = False
End With
.AddDataField .PivotFields("Debit"), "Sum of Debit", xlSum
.AddDataField .PivotFields("Credit"), "Sum of Credit", xlSum
.AddDataField .PivotFields("Balance"), "Sum of Balance", xlSum
.PivotFields("Sum of Debit").NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
.PivotFields("Sum of Credit").NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
.PivotFields("Sum of Balance").NumberFormat = "_-£* #,##0_-;-£* #,##0_-;_-£* ""-""_-;_-@_-"
End With
End Sub