Sub ExistingMacro()
Dim lr As Long
Dim strDir As String, DflDir As String, YrDir As String
DflDir = "Z:\Orders\2-Open Trades\"
YrDir = (DflDir & Format(Date, "yyyy"))
strDir = (YrDir & "\" & Format(Date, "mm") & " " & Format(Date, "mmmm"))
lr = Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet1").Delete
Sheets.Add.Name = "Pivot"
Range("B2").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
:="Pivot!R2C2", TableName:="PivotTable10", DefaultVersion:= _
xlPivotTableVersion15
Sheets("Pivot").Select
Cells(2, 2).Select
With ActiveSheet.PivotTables("PivotTable10").PivotFields( _
"Investment Product: Strike Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable10").PivotFields( _
"Investment Product: Investment Products Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable10").PivotFields( _
"Investment Product: Additional Info")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
"PivotTable10").PivotFields("Investment Product: Total Hedged"), _
"Sum of Investment Product: Total Hedged", xlSum
With ActiveSheet.PivotTables("PivotTable10").PivotFields( _
"Sum of Investment Product: Total Hedged")
.Caption = "Average of Investment Product: Total Hedged"
.Function = xlAverage
End With
ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
"PivotTable10").PivotFields("Nominal"), "Sum of Nominal", xlSum
ActiveSheet.PivotTables("PivotTable10").PivotFields("Investment Product: ISIN") _
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields( _
"Investment Product: Investment Products Name").Subtotals = Array(False, False, _
False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields( _
"Investment Product: Additional Info").Subtotals = Array(False, False, False, _
False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields( _
"Investment Product: Strike Date").Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields("Investment Currency"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields( _
"Investment Product: Total Hedged").Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields("Nominal").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields("Issue Price (%)"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields("IPS Date").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields("Order Received"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable10").PivotFields("Bank Order").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("PivotTable10")
.ColumnGrand = False
.RowGrand = False
End With
With ActiveSheet.PivotTables("PivotTable10")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
Columns("B:B").ColumnWidth = 19.29
Columns("C:C").ColumnWidth = 75.86
Columns("C:C").ColumnWidth = 80.71
Columns("D:D").ColumnWidth = 19.14
Columns("E:E").ColumnWidth = 18.29
Rows("3:3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("E:F").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
Selection.NumberFormat = "_-* #,##0_ -;-* #,##0_ -;_-* ""-""??_-;_-@_-"
Range("E33").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-29]C:R[-1]C)"
Range("F33").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-29]C:R[-1]C)"
Range("D33").Select
ActiveCell.FormulaR1C1 = "Totals"
Selection.Font.Bold = True
Selection.HorizontalAlignment = xlCenter
Range("H3").Select
ActiveCell.FormulaR1C1 = "Shortfall (+ve) / Top Up Required (-ve)"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.ColumnWidth = 13.71
.Font.Bold = True
End With
Range("H4:H33").Select
Selection.NumberFormat = "_-* #,##0_ -;-* #,##0_ -;_-* ""-""??_-;_-@_-"
With Sheets("Pivot")
lr = .Range("E" & .Rows.Count).End(xlUp).Row
If lr > 3 Then .Range("H4:H" & lr).FormulaR1C1 = "=IF(RC[-3]>0,RC[-3]-RC[-2],"""")"
End With
Range("A1").Select
Sheets("Paul All Current Orders").Select
Columns("A:A").ColumnWidth = 17
Columns("B:B").ColumnWidth = 77
Columns("C:C").ColumnWidth = 23
Columns("D:K").ColumnWidth = 14
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
Columns("F:G").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
Selection.NumberFormat = "_-* #,##0_ -;-* #,##0_ -;_-* ""-""??_-;_-@_-"
End With
With Range("A:A")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
With Range("A2:A500")
.HorizontalAlignment = xlCenter
End With
With Range("D2:E500")
.HorizontalAlignment = xlCenter
End With
With Range("H2:K500")
.HorizontalAlignment = xlCenter
End With
If Dir(DflDir, vbDirectory) = "" Then
MkDir DflDir
Else
End If
If Dir(YrDir, vbDirectory) = "" Then
MkDir YrDir
Else
End If
If Dir(strDir, vbDirectory) = "" Then
MkDir strDir
Else
End If
ChDir strDir
Filename = Format(Now, "yyyy-mm-dd")
ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=52
End Sub