VB Code is correct but table fromats not getting updated.

Hi,

I have written a code for formatting the table to specific format, If I put in debugger mode and run the program means its getting updated correctly, but if I run directly the table is not getting updated . can you help in finding the issue. I attached the excel sheet for the table

Its working till the point of selecting the table for applying the borders, but its not applying borders.

Sub Pivot_Macro() 
     'Pivot Table Macro
    Dim lRow As Long 
    Set wb1 = ThisWorkbook 
    Dim ws1 As Worksheet, ws4 As Worksheet, ws5 As Worksheet 
    Set ws1 = wb1.Worksheets("Sheet1") 
    Set ws4 = wb1.Worksheets("AGING") 
    Set ws5 = wb1.Worksheets("Sheet3") 
    With ws1 
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row 
        MsgBox (lRow) 
    End With 
    Dim pt As PivotTable 
    Dim cacheOfpt As PivotCache 'this is the source data of pt
    Dim pf As PivotField 
    Dim pi As PivotItem 
    Dim PTlRow As Long 
    Dim PTlColumn As Long 
    With ws4 
         
        On Error Resume Next 
        Sheets("AGING").Select 
        ActiveSheet.PivotTables("Distribution of Orders across the age").TableRange2.Clear 'delete the pivot table if any exists
         'set the cache of pt
        Sheets("Sheet1").Select 
        Set cacheOfpt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1:Z" & lRow)) 
         'Create the pt
        Sheets("AGING").Select 
        Set pt = ActiveSheet.PivotTables.Add(cacheOfpt, Range("A1"), "Distribution of Orders across the age") 
         'put fields in
        With pt 
             'add the fields
            .PivotFields("BUCKET").Orientation = xlRowField 
            .PivotFields("ROOTORDERTYPE").Orientation = xlRowField 
            .PivotFields("Aging").Orientation = xlColumnField 
            .PivotFields("ORDER_NUM").Orientation = xlDataField 
             'go to classic view
            .RowAxisLayout xlTabularRow 
        End With 
         
        PTlRow = .Range("A" & .Rows.Count).End(xlUp).Row 
        MsgBox (PTlRow) 
         
        PTlColumn = ActiveSheet.UsedRange.Columns.Count 
        MsgBox (PTlColumn) 
         
        .Range("A2:H" & PTlRow).Select 
        Selection.Copy 
         
        With ws5.Range("A1") 
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
            :=False, Transpose:=False 
            .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ 
            SkipBlanks:=False, Transpose:=False 
        End With 
    End With 
     
    With ws5 
        PTlRow = .Range("A" & .Rows.Count).End(xlUp).Row 
        MsgBox (PTlRow) 
         
        PTlColumn = .Cells(1, Columns.Count).End(xlToLeft).Column 
        MsgBox (PTlColumn) 
         
        Cells.Range("A1:H" & PTlRow).EntireColumn.AutoFit 
        .Range("A1:H" & PTlRow).Select 
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
        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 
         
        Dim arrColOrder As Variant, ndx As Integer 
        Dim Found As Range, counter As Integer 
        arrColOrder = Array("BUCKET", "ROOTORDERTYPE", "1-2 days", "2-5 days", "5-10 days", "10-30 days", "30 days+", "Grand Total") 
        counter = 1 
         
        Application.ScreenUpdating = False 
         
        For ndx = LBound(arrColOrder) To UBound(arrColOrder) 
             
            Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _ 
            SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) 
             
            If Not Found Is Nothing Then 
                If Found.Column <> counter Then 
                    Found.EntireColumn.Cut 
                    Columns(counter).Insert Shift:=xlToRight 
                    Application.CutCopyMode = False 
                End If 
                counter = counter + 1 
            End If 
        Next ndx 
         
        Application.ScreenUpdating = True 
    End With 
     
    wb1.Save 
     
End Sub

It is difficult to test you code as there are a number of sheets that are missing. You have attached sheet3 only.
If you say the error occurs on for applying the borders, I suspect this is because you have with statement and then you start selecting the ranges which is bad idea IMO. You have already set up you with statement, so avoid using select. I have tried to do it, but I am not sure as I can not test it. I hope these lines would give you a good start

  With .Range("A1:H" & PTlRow)
           .Borders(xlDiagonalDown).LineStyle = xlNone
          With .Borders(xlDiagonalUp).LineStyle = xlNone
          .Borders (xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
     
        .Borders (xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        
        .Borders (xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
       
         .Borders (xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
      
        .Borders (xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
       
        .Borders (xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
         End With
     End With

Hi,

I did the changes but still its not working. I attached the excel sheet with all details and macro in it. Please look in to pivot macro in it.


Thanks in Advance,
Menaka B

Menaka,
I have now tested your code. There is no error and all the lines of the code are working as intended. The bordering occurs on sheet3. I have removed them and run the code and it does all the bordering and lining.