run time error 1004: The formula is not complete. Make sure an ending square bracker ] is not missing

pitatski

New Member
Joined
Jun 4, 2014
Messages
2
I have a saved macro where I've been using for the longest time with no issue unless the rows of my raw data are more than the usual.
I'm currently using Excel 2010. When my raw data is just 10,000 rows, macro runs with no issues but when raw data is more than 10,000 rows, this is what i get, "run time error 1004: The formula is not complete. Make sure an ending square bracker ] is not missing." I clicked debug and below is the highlighted script. The issue here should be the number of rows but I don't understand why and how to correct. Can anyone please help me.

ActiveSheet.PivotTables("PivotTable3").PivotSelect "Assignment[All;Total]", _ xlDataAndLabel, True
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
It might be best to post the whole code.... but my guess would be this ... from the Microsoft website

PivotTable and PivotChart report specifications and limits
FEATURE MAXIMUM LIMIT
PivotTable reports on a sheet Limited by available memory
Unique items per field 1,048,576
Row or column fields in a PivotTable report Limited by available memory
Report filters in a PivotTable report 256 (may be limited by available memory)
Value fields in a PivotTable report 256
Calculated item formulas in a PivotTable report Limited by available memory
Report filters in a PivotChart report 256 (may be limited by available memory)
Value fields in a PivotChart report 256
Calculated item formulas in a PivotChart report Limited by available memory
Length of the MDX name for a PivotTable item 32,767
Length for a relational PivotTable string 32,767
<B> Items displayed in filter drop-down lists10,000 <B>
 
Upvote 0
Thanks for the reply. Here is the code. What should I add so that it can process more than 10,000 rows.
Code:
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
 
Last edited by a moderator:
Upvote 0
Does this work for you?

Code:
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Assignment").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False)
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top