Sub AR_MACRO_NEW()
'
' AR_MACRO_NEW Macro
' Macro recorded 4/25/2013 by emorale6
'Create New Sheet
Sheets.Add.Name = "Output"
Sheets("Raw Data").Select
Cells.Select
Selection.Copy
'Create Output Sheet
Sheets("Output").Select
Cells.Select
ActiveSheet.Paste
Rows("1:4").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("A1").Value = "Holcim Philippines, Inc."
Range("A2").Value = "BSC-O2C: Daily AR Monitoring for analysis"
Range("A3").Value = "2013"
Range("A1:A3").Select
Selection.Font.Bold = True
Columns("R:R").Select
ActiveWindow.SmallScroll ToRight:=2
Dim outputlastrow As String
outputlastrow = Columns(1).Find("*", searchdirection:=xlPrevious).Row
Columns("R:T").Select
Selection.Insert Shift:=xlToRight
Range("R5").Value = "CWT"
Range("S5").Value = "Doc No."
Range("T5").Value = "Remarks"
Range("R5:T5").Select
Selection.Interior.ColorIndex = 6
Selection.Font.Bold = True
Columns("P:Q").Select
Selection.Insert Shift:=xlToRight
Range("P5").Value = "SG"
Range("Q5").Value = "DD"
Range("P5:Q5").Select
Selection.Interior.ColorIndex = 6
Selection.Font.Bold = True
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("E5").Select
ActiveCell.FormulaR1C1 = "Year"
Range("F5").Select
ActiveCell.FormulaR1C1 = "Backlog/ CY"
Range("F1").Select
Columns("F:F").ColumnWidth = 11.29
Columns("E:F").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("E5:F5").Select
Selection.Font.Bold = True
Range("E6").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
Range("E6").Select
Selection.AutoFill Destination:=Range("E6:E" & outputlastrow)
Range("F6").FormulaR1C1 = "=IF(RC[-2]>=""2013/04"",""CY"",""Backlog"")"
Range("F6").Select
Selection.AutoFill Destination:=Range("F6:F" & outputlastrow)
Range("X6").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"Advanced Collection,Balance after FIFO clearing,Cash Bond Interest,CM/Rebates,E-payment not yet due,For RFA,Identified with Payment Variance,Invoice Reversal,Lack Payment Reference,Legal Account,Overpayment,Pending review of OTC,Price Difference Overpayment,Tonner Bags Deposit,Unmatched Collection,Unmatched CWT,Unserved Order,Write-Off"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("X6").Select
Selection.AutoFill Destination:=Range("X6:X" & outputlastrow)
'Sort
Range("A5:Z" & outputlastrow).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Key2:=Range("B6") _
, Order2:=xlAscending, Key3:=Range("C6"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, _
DataOption3:=xlSortTextAsNumbers
'Create Lookup of Customer
Range("AA5").Value = "Customer Name"
Columns("AA:AA").EntireColumn.AutoFit
Range("AA5").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("AA6").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-26],'STATIC FILE'!R11C4:R5000C5,2,FALSE)"
Range("AA6").Select
Selection.AutoFill Destination:=Range("AA6:AA" & outputlastrow)
'Lookup CQJ
Range("AB5").Select
ActiveCell.FormulaR1C1 = "CQJ Indicator"
Range("AB6").Select
ActiveCell.FormulaR1C1 = "=RC[-14]&RC[-11]"
Range("AB6").Select
Selection.AutoFill Destination:=Range("AB6:AB" & outputlastrow)
'Lookup CQ/HO/DG Only
Range("AC5").Select
ActiveCell.FormulaR1C1 = "CQ/HO/DG only"
Range("AC6").Select
Columns("AC:AC").EntireColumn.AutoFit
Range("AA5:AC5").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Range("AC6").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'STATIC FILE'!R11C11:R133C13,3,FALSE)"
Range("AC6").Select
Selection.AutoFill Destination:=Range("AC6:AC" & outputlastrow)
Columns("H:H").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select
Selection.Cut
Columns("C:C").Select
Columns("Y:Y").Select
Selection.Cut
Columns("V:V").Select
Columns("L:L").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("A5:AC" & outputlastrow).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Key2:=Range("B6") _
, Order2:=xlAscending, Key3:=Range("C6"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, _
DataOption3:=xlSortTextAsNumbers
'Create line item lookup sheet
Sheets("Output").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Output!R5C1:R" & outputlastrow & "C27").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Customer")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Clearing Document"), "Count of Clearing Document", _
xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Customer Name")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").PivotSelect "Customer[All;Total]", _
xlDataAndLabel, True
Selection.Delete
ActiveSheet.Name = "Clearing Document Count"
'Create Pivot
'By Assignment
Sheets("Output").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Output'!R5C1:R" & outputlastrow & "C29").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Customer")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Customer Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Assignment")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Document Number")
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("PivotTable3").PivotSelect "Assignment[All;Total]", _
xlDataAndLabel, True
Selection.Delete
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Text")
.Orientation = xlRowField
.Position = 5
End With
ActiveSheet.PivotTables("PivotTable3").PivotSelect _
"'Document Number'[All;Total]", xlDataAndLabel, True
Selection.Delete
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Document Type")
.Orientation = xlRowField
.Position = 6
End With
Range("C4").Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Document Type")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Amount in local currency"), _
"Sum of Amount in local currency", xlSum
ActiveSheet.PivotTables("PivotTable3").PivotSelect "Customer[All;Total]", _
xlDataAndLabel, True
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Columns("D:AZ").Select
Selection.Style = "Comma"
ActiveSheet.Select
ActiveSheet.Name = "By Assignment"
Sheets("By Assignment").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "'Customer Name'[All;Total]" _
, xlDataAndLabel, True
Selection.Delete
ActiveSheet.PivotTables("PivotTable3").PivotFields("Document Type").PivotItems( _
"RV").Position = 1
Columns("F:M").Select
Columns("F:M").EntireColumn.AutoFit
Selection.Style = "Comma"
'By Payment Reference
Sheets("By Assignment").Select
Sheets("By Assignment").Copy Before:=Sheets(5)
Range("C6").Select
ActiveWorkbook.ShowPivotTableFieldList = True
Range("C4").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("Assignment").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Payment reference")
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("PivotTable3").PivotSelect _
"'Payment reference'[All;Total]", xlDataAndLabel, True
Selection.Delete
ActiveWorkbook.ShowPivotTableFieldList = False
Columns("D:Q").Select
Columns("D:Q").EntireColumn.AutoFit
Columns("F:M").Select
Range("M1").Activate
Selection.Style = "Comma"
Sheets("By Assignment (2)").Select
Sheets("By Assignment (2)").Name = "By Payment Reference"
'By Document Header Text
Sheets("By Payment Reference").Select
Sheets("By Payment Reference").Copy Before:=Sheets(6)
Sheets("By Payment Reference (2)").Select
Sheets("By Payment Reference (2)").Name = "By Doc Header Text"
Range("D4").Select
ActiveWorkbook.ShowPivotTableFieldList = True
Range("D4").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("Payment reference"). _
Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Document Header Text")
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("PivotTable3").PivotSelect _
"'Document Header Text'[All;Total]", xlDataAndLabel, True
Selection.Delete
Columns("D:AJ").Select
Columns("D:AJ").EntireColumn.AutoFit
Range("Y7").Select
Columns("F:M").Select
Range("M1").Activate
Selection.Style = "Comma"
'By Invoice Reference
Sheets("By Doc Header Text").Select
Sheets("By Doc Header Text").Copy Before:=Sheets(7)
Range("N29").Select
Sheets("By Doc Header Text (2)").Select
Sheets("By Doc Header Text (2)").Name = "By Invoice Reference"
Range("D4").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("Document Header Text"). _
Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Invoice reference")
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("PivotTable3").PivotSelect _
"'Invoice reference'[All;Total]", xlDataAndLabel, True
Selection.Delete
Columns("D:P").Select
Columns("D:P").EntireColumn.AutoFit
Columns("F:M").Select
Range("M1").Activate
Selection.Style = "Comma"
Range("O17").Select
' CWT Computation
Sheets(Array("By Assignment", "By Payment Reference", "By Doc Header Text", _
"By Invoice Reference")).Select
'Sheets("By Assignment").Activate
Cells.Select
Range("H1").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim analysis_lastrow As String
Dim analysis_lastcolumn, nextcell, firstcell As Integer
analysis_lastrow = Columns(1).Find("*", searchdirection:=xlPrevious).Row
analysis_lastcolumn = Rows(4).Find("*", searchdirection:=xlPrevious).Column
nextcell = analysis_lastcolumn + 1
firstcell = 1 + 5
Cells(4, nextcell).Value = "CWT"
Cells(5, nextcell).Value = "=RC[-10]/112"
Cells(5, nextcell).Select
Selection.AutoFill Destination:=Range(Cells(5, nextcell), Cells(analysis_lastrow, nextcell))
Application.CutCopyMode = False
Range(Cells(4, nextcell), Cells(analysis_lastrow, nextcell)).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
'Range("N5:N5166").Select
'ActiveWindow.SmallScroll Down:=-6
Range(Cells(4, nextcell - 1), Cells(analysis_lastrow, nextcell - 1)).Select
Selection.Cut
Range(Cells(4, 6), Cells(analysis_lastrow, 6)).Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Interior.ColorIndex = 36
Selection.Font.ColorIndex = 1
Selection.Font.Bold = True
Sheets("By Payment Reference").Activate
Cells(4, nextcell).Value = "CWT"
Cells(5, nextcell).Value = "=RC[-10]/112"
Cells(5, nextcell).Select
Selection.AutoFill Destination:=Range(Cells(5, nextcell), Cells(analysis_lastrow, nextcell))
Application.CutCopyMode = False
Range(Cells(4, nextcell), Cells(analysis_lastrow, nextcell)).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Range(Cells(4, nextcell - 1), Cells(analysis_lastrow, nextcell - 1)).Select
Selection.Cut
Range(Cells(4, 6), Cells(analysis_lastrow, 6)).Insert Shift:=xlToRight
Sheets("By Doc Header Text").Activate
Cells(4, nextcell).Value = "CWT"
Cells(5, nextcell).Value = "=RC[-10]/112"
Cells(5, nextcell).Select
Selection.AutoFill Destination:=Range(Cells(5, nextcell), Cells(analysis_lastrow, nextcell))
Application.CutCopyMode = False
Range(Cells(4, nextcell), Cells(analysis_lastrow, nextcell)).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Range(Cells(4, nextcell - 1), Cells(analysis_lastrow, nextcell - 1)).Select
Selection.Cut
Range(Cells(4, 6), Cells(analysis_lastrow, 6)).Insert Shift:=xlToRight
Sheets("By Invoice Reference").Activate
Cells(4, nextcell).Value = "CWT"
Cells(5, nextcell).Value = "=RC[-10]/112"
Cells(5, nextcell).Select
Selection.AutoFill Destination:=Range(Cells(5, nextcell), Cells(analysis_lastrow, nextcell))
Application.CutCopyMode = False
Range(Cells(4, nextcell), Cells(analysis_lastrow, nextcell)).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Range(Cells(4, nextcell - 1), Cells(analysis_lastrow, nextcell - 1)).Select
Selection.Cut
Range(Cells(4, 6), Cells(analysis_lastrow, 6)).Insert Shift:=xlToRight
'Color coding of sheet
Sheets(Array("Clearing Document Count", "By Assignment", "By Payment Reference", _
"By Doc Header Text", "By Invoice Reference")).Select
Sheets("By Invoice Reference").Activate
ActiveWorkbook.Sheets("By Invoice Reference").Tab.ColorIndex = 6
ActiveWorkbook.Sheets("By Doc Header Text").Tab.ColorIndex = 6
ActiveWorkbook.Sheets("By Payment Reference").Tab.ColorIndex = 6
ActiveWorkbook.Sheets("By Assignment").Tab.ColorIndex = 6
ActiveWorkbook.Sheets("Clearing Document Count").Tab.ColorIndex = 6
'Create Summary
' Beginning Balance
Sheets("Output").Select
Range("A5").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Output!R5C1:R" & outputlastrow & "C29").CreatePivotTable TableDestination:="", TableName:= _
"summary_table", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("summary_table").PivotFields("Customer")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("summary_table").PivotFields("Customer Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("summary_table").PivotFields("CQ/HO/DG only")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("summary_table").PivotFields("CQ/HO/DG only"). _
CurrentPage = "Show"
ActiveSheet.PivotTables("summary_table").AddDataField ActiveSheet.PivotTables( _
"summary_table").PivotFields("Amount in local currency"), _
"Sum of Amount in local currency", xlSum
With ActiveSheet.PivotTables("summary_table").PivotFields("Document Type")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("summary_table").PivotFields("Backlog/ CY")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("summary_table").PivotSelect _
"'Document Type'[All;Total] Show", xlDataAndLabel, True
Selection.Delete
ActiveSheet.PivotTables("summary_table").PivotSelect "Customer[All;Total] Show", _
xlDataAndLabel, True
Selection.Delete
ActiveSheet.Name = "Summary"
'Cleared Items
Dim sum_lastrow As String
Dim sum_lastcolumn As Integer
sum_lastrow = Columns(1).Find("*", searchdirection:=xlPrevious).Row
sum_lastcolumn = Rows(6).Find("*", searchdirection:=xlPrevious).Column
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Output!R5C1:R" & outputlastrow & "C29").CreatePivotTable TableDestination:= _
"'[Macro Template_Daily AR Monitoring_FINAL 5.6 version3.0.xls]Summary'!R3C" & sum_lastcolumn + 4, _
TableName:="AAA", DefaultVersion:=xlPivotTableVersion10
'ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Output!R5C1:R" & outputlastrow & "C29").CreatePivotTable TableDestination:= _
"'[Macro Template_Daily AR Monitoring_FINAL 5.6 version2.0.xls]Summary'!R3C" & sum_lastcolumn + 21, _
TableName:="remarks pivot", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("AAA").PivotFields("Customer")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("AAA").PivotFields("Backlog/ CY")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("AAA").PivotFields("Document Type")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("AAA").PivotSelect "'Document Type'[All;Total]" _
, xlDataAndLabel, True
Selection.Delete
ActiveSheet.PivotTables("AAA").AddDataField ActiveSheet.PivotTables( _
"AAA").PivotFields("Amount in local currency"), _
"Sum of Amount in local currency", xlSum
With ActiveSheet.PivotTables("AAA").PivotFields("CQ/HO/DG only")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("AAA").PivotFields("CQ/HO/DG only"). _
CurrentPage = "Show"
With ActiveSheet.PivotTables("AAA").PivotFields("Doc No.")
.Orientation = xlPageField
.Position = 1
End With
Dim sum_lastcolumn_with_clearitems, sum_lastrow_with_clearitems As Integer
sum_lastcolumn_with_clearitems = Rows(6).Find("*", searchdirection:=xlPrevious).Column
sum_lastrow_with_clearitems = Columns(1).Find("*", searchdirection:=xlPrevious).Row
'Range(Cells(3, sum_lastcolumn + 3), Cells(5, sum_lastcolumn + 8)).Select
Cells(3, sum_lastcolumn_with_clearitems + 3).Value = "ENDING BALANCE"
Cells(4, sum_lastcolumn_with_clearitems + 3).Value = "CQ"
Cells(5, sum_lastcolumn_with_clearitems + 3).Value = "CY"
Cells(4, sum_lastcolumn_with_clearitems + 4).Value = "CQ"
Cells(5, sum_lastcolumn_with_clearitems + 4).Value = "Backlog"
Cells(4, sum_lastcolumn_with_clearitems + 5).Value = "DG"
Cells(5, sum_lastcolumn_with_clearitems + 5).Value = "CY"
Cells(4, sum_lastcolumn_with_clearitems + 6).Value = "DG"
Cells(5, sum_lastcolumn_with_clearitems + 6).Value = "Backlog"
Cells(4, sum_lastcolumn_with_clearitems + 7).Value = "HO"
Cells(5, sum_lastcolumn_with_clearitems + 7).Value = "CY"
Cells(4, sum_lastcolumn_with_clearitems + 8).Value = "HO"
Cells(5, sum_lastcolumn_with_clearitems + 8).Value = "Backlog"
Cells(5, sum_lastcolumn_with_clearitems + 9).Value = "GRANDTOTAL"
'Range(Cells(3, sum_lastcolumn_with_clearitems + 10), Cells(5, sum_lastcolumn_with_clearitems + 15)).Select
Cells(5, sum_lastcolumn_with_clearitems + 10).Value = "Line Items"
Cells(6, sum_lastcolumn_with_clearitems + 10).Value = "=VLOOKUP(RC[-28],'Clearing Document Count'!R[-1]C[-28]:R[9994]C[-26],3,FALSE)"
Cells(6, sum_lastcolumn_with_clearitems + 10).Select
Selection.AutoFill Destination:=Range(Cells(6, sum_lastcolumn_with_clearitems + 10), Cells(sum_lastrow_with_clearitems, sum_lastcolumn_with_clearitems + 10))
Rows("3:3").Select
Selection.Interior.ColorIndex = 3
Selection.Font.ColorIndex = 2
Selection.Font.Bold = False
Selection.Font.Bold = True
Range(Cells(5, sum_lastcolumn_with_clearitems + 10), Cells(sum_lastrow_with_clearitems, sum_lastcolumn_with_clearitems + 10)).Select
Selection.Interior.ColorIndex = 1
Selection.Font.ColorIndex = 2
Selection.Font.Bold = True
Rows("4:5").Select
Selection.Font.Bold = True
Range(Cells(4, sum_lastcolumn_with_clearitems + 3), Cells(sum_lastrow_with_clearitems, sum_lastcolumn_with_clearitems + 9)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'Remarks Pivot
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Output!R5C1:R" & outputlastrow & "C29").CreatePivotTable TableDestination:= _
"'[Macro Template_Daily AR Monitoring_FINAL 5.6 version3.0.xls]Summary'!R3C" & sum_lastcolumn_with_clearitems + 13, _
TableName:="RemarksPivot", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("RemarksPivot").PivotFields("Customer")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("RemarksPivot").PivotFields("Customer Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("RemarksPivot").PivotFields("CQ/HO/DG only")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("RemarksPivot").PivotFields("CQ/HO/DG only"). _
CurrentPage = "Show"
ActiveSheet.PivotTables("RemarksPivot").AddDataField ActiveSheet.PivotTables( _
"RemarksPivot").PivotFields("Amount in local currency"), _
"Sum of Amount in local currency", xlSum
With ActiveSheet.PivotTables("RemarksPivot").PivotFields("Backlog/ CY")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("RemarksPivot").PivotFields("Remarks")
.Orientation = xlColumnField
.Position = 2
End With
ActiveSheet.PivotTables("RemarksPivot").PivotSelect _
"'Backlog/ CY'[All;Total] Show", xlDataAndLabel, True
Selection.Delete
ActiveSheet.PivotTables("RemarksPivot").PivotSelect "Customer[All;Total] Show" _
, xlDataAndLabel, True
Selection.Delete
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("RemarksPivot").PivotFields("Document Type")
.Orientation = xlColumnField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("RemarksPivot").PivotSelect _
"'Document Type'[All;Total] Show", xlDataAndLabel, True
Selection.Delete
Dim sum_lastcolumn_with_clearitems2 As Integer
sum_lastcolumn_with_clearitems2 = Rows(6).Find("*", searchdirection:=xlPrevious).Column
Range(Cells(1, sum_lastcolumn_with_clearitems + 13), Cells(sum_lastrow_with_clearitems, sum_lastcolumn_with_clearitems2 + 1)).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
'shift table down
Range("A1").Select
Range(Cells(1, 1), Cells(sum_lastrow, sum_lastcolumn)).Select
Selection.Cut Destination:=Range(Cells(2, 1), Cells(sum_lastrow, sum_lastcolumn))
Range("A2:I52").Select
Rows("3:3").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
With Selection.Font
.Name = "Arial"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A3").Value = "BEGINNING BALANCE"
Cells(3, sum_lastcolumn + 4).Value = "CLEARED ITEMS"
Range(Cells(1, 1), Cells(sum_lastrow + 1, sum_lastcolumn)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'put header
Rows("1:4").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Holcim Philippines, Inc."
Range("A2").Select
ActiveCell.FormulaR1C1 = "BSC-O2C: Summary AR Analysis"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("A3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:A3").Select
Selection.Font.Bold = True
Range("A1").Select
'fix placing of sheet
Sheets(Array("Summary", "Output")).Select
Sheets("Output").Activate
ActiveWorkbook.Sheets("Output").Tab.ColorIndex = 4
ActiveWorkbook.Sheets("Summary").Tab.ColorIndex = 4
Sheets(Array("Summary", "Output")).Select
Sheets("Summary").Activate
Sheets(Array("Summary", "Output")).Move Before:=Sheets(3)
Sheets("Clearing Document Count").Select
Sheets("Clearing Document Count").Move Before:=Sheets(5)
Sheets("Summary").Select
MsgBox "Summary and Analysis Sheet Completed!You may now begin AR Clearing!"
End Sub