Otimizando o Código VBA e melhorando o desempenho

Dec 14 2020

Eu desenvolvi uma macro VBA que é usada para atualizar as 5 consultas SAP AAO e, em seguida, copiar os dados dessas consultas em tabelas de maneira adequada, removendo duplicatas e, em seguida, carregadas na consulta avançada. Lá eu adiciono algumas colunas calculadas e, em seguida, carrego os dados nos pivôs de energia.

Eu também usei o código VBA mencionado abaixo para executar algumas ações em cada uma das planilhas, porém atualmente está demorando mais de 4 minutos para executar o código.

Nesses 4 minutos, pelo menos 45 segundos são necessários para atualizar as consultas AAO e o tempo restante da macro para ser executado (incluindo a atualização das consultas avançadas)

Ainda estou aprendendo VBA e, portanto, usei código de gravador de macro e também select / activateplanilhas em meu código. Eu sei que não é o caminho certo, pois não conheço a melhor opção que não seja esta. Por causa dessas coisas, a macro está demorando mais para ser executada.

Se eu não usar o selectcódigo para selecionar as planilhas, recebo uma mensagem de erro e o código não está sendo executado.

Seria ótimo se alguém pudesse me ajudar a otimizar este código, para que demore menos tempo para realizar operações de macro.

Sub Refresh_AAO_Query()
Dim MyArray, i
Dim Sht, Sht1, Sht2, Sht3, Sht4, Sht5 As Worksheet
Dim OPs_Model_Data, OPs_Data, Sales_Data, Threats_Data, RE_Turnover, RE_LY As Worksheet
Dim Message As String
Dim LastRow1, LastRow2, LastRow3, LastRow4, LastRow5, LastRow6, LoB_LastRow, LoB_LastRow1, LoB_LastRow2, LoB_LastRow3, LastColumn1, LastColumn2, LastColumn3, LastColumn4, LastColumn5, LastColumn6, LastColumn7 As Long
Dim LoB, LoB1, LoB2, LoB3 As ListObject
Dim StartCell0, StartCell1, StartCell2, StartCell3, StartCell4, StartCell5, StartCell6, StartCell7, StartCell8, StartCell9 As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheet2.Visible = xlSheetVisible
Sheet4.Visible = xlSheetVisible
Sheet5.Visible = xlSheetVisible
Sheet9.Visible = xlSheetVisible
Sheet10.Visible = xlSheetVisible
Sheet11.Visible = xlSheetVisible
Sheet12.Visible = xlSheetVisible
Sheet16.Visible = xlSheetVisible
 
Enable_Analysis_For_Office
Refresh_AAO_Queries

' Fixing "Andhra Pradesh" state issue

MyArray = Array("Sales_Data", "Threats_Data", "RE_Turnover", "RE_LY")

    For Each i In MyArray
        
        If Sheets(i).Name <> Sheets("Sales_Data").Name Then
           Sheets(i).Activate
            Sheets(i).Range("A2:K2").Select
        
            Selection.AutoFilter
            ActiveSheet.Range("$A$2:$K$50000").AutoFilter Field:=4, Criteria1:= _
                "=Teshima", Operator:=xlOr, Criteria2:="=Teshima?Kaken"
            Columns("D:D").Select
            Selection.SpecialCells(xlCellTypeVisible).Select
            Selection.Replace What:="Andhra?Pradesh", Replacement:="Andhra Pradesh", _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
                False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            Selection.AutoFilter
        Else
            Sheets(i).Activate
            Range("A2:Q2").Select
            Selection.AutoFilter
            ActiveSheet.Range("$A$3:$Q$50000").AutoFilter Field:=4, Criteria1:= _
                "=Teshima", Operator:=xlOr, Criteria2:="=Teshima?Kaken"
            Columns("D:D").Select
            Selection.SpecialCells(xlCellTypeVisible).Select
            Selection.Replace What:="Andhra?Pradesh", Replacement:="Andhra Pradesh", _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
                False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            Selection.AutoFilter
        End If
        
    Next i
    
' Finding Last Row and Column for("OPs_Model_Data") worksheet

Set Sht = Sheet12
Set Sht1 = Sheet4
Set StartCell0 = Sheet12.Range("A3")
Set StartCell1 = Sheet4.Range("A2")

    Sheet4.Select

        LastRow1 = Sht1.Cells(Sht1.Rows.Count, StartCell1.Column).End(xlUp).Row
        LastColumn1 = Sht1.Cells(StartCell1.Row, Sht1.Columns.Count).End(xlToLeft).Column
        Sht1.Range(StartCell1, Sht1.Cells(LastRow1, LastColumn1)).Select
        Selection.EntireRow.Delete
    
    Sheet12.Select
    
        LastRow2 = Sht.Cells(Sht.Rows.Count, StartCell0.Column).End(xlUp).Row
        LastColumn2 = Sht.Cells(StartCell0.Row, Sht.Columns.Count).End(xlToLeft).Column
    
        Sht.Range(StartCell0, Sht.Cells(LastRow2, LastColumn2)).Select
        Selection.Copy
        
        Worksheets("OPs_Model_Data").Range("A2").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    
    Sheet12.Range("A1").Select
    
' Refreshing the "Table2" Query

        ActiveWorkbook.Connections("Query - Table2").Refresh

' Finding Last Row and Column and updating the data for("RE_LY") worksheet

    Sheet11.Select

Set Sht3 = Sheet11
Set LoB1 = Sht3.ListObjects("RE_LY_Table")
Set StartCell4 = Sheet11.Range("A4")

    Sht3.ListObjects("RE_LY_Table").DataBodyRange.Delete
        
        LastRow4 = Sht3.Cells(Sht3.Rows.Count, StartCell4.Column).End(xlUp).Row
        LastColumn4 = Sht3.Cells(StartCell4.Row, Sht3.Columns.Count).End(xlToLeft).Column
        
    Sht3.Range("A1:K1").Copy
    Sht3.Range("A3:K3").PasteSpecial Paste:=xlPasteValues
        
    Sht3.Range("A3:J" & LastRow4).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("RE_LY_Table[Business Function]"), Unique:=True

Set StartCell5 = Sheet11.Range("AA4")
       
        LoB1_LastRow = Sht3.Cells(Sht3.Rows.Count, StartCell5.Column).End(xlUp).Row - 1
        
        LoB1.Resize LoB1.Range.Resize(LoB1_LastRow)
            
    Sht3.Range("AA2:AK2").Copy
    Sht3.Range("AA3:AK3").PasteSpecial Paste:=xlPasteValues
    
       Range("AK4").FormulaR1C1 = "=SUMIFS(C[-26],C[-36],RC[-10],C[-35],RC[-9],C[-34],RC[-8],C[-33],RC[-7],C[-32],RC[-6],C[-31],RC[-5],C[-30],RC[-4],C[-29],RC[-3],C[-28],RC[-2],C[-27],RC[-1])"
       Range("AK4").Select
       Selection.Copy
            Range(Selection, Selection.End(xlDown)).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Selection.Copy
            Sht3.Range("AK4").PasteSpecial Paste:=xlPasteValues
            Selection.NumberFormat = "0.0"
    
    Sheet11.Range("A1").Select
                
' Finding Last Row and Column and updating the data for("RE_Turnover") worksheet

    Sheet2.Select

Set Sht4 = Sheet2
Set LoB2 = Sht4.ListObjects("RE_Turnover_Table")
Set StartCell6 = Sheet2.Range("A4")

    Sht4.ListObjects("RE_Turnover_Table").DataBodyRange.Delete
        
        LastRow5 = Sht4.Cells(Sht4.Rows.Count, StartCell6.Column).End(xlUp).Row
        LastColumn5 = Sht4.Cells(StartCell6.Row, Sht4.Columns.Count).End(xlToLeft).Column + 1
        
    Sht4.Range("A1:K1").Copy
    Sht4.Range("A3:K3").PasteSpecial Paste:=xlPasteValues
        
    Sht4.Range("A3:J" & LastRow5).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("RE_Turnover_Table[Business Function]"), Unique:=True

Set StartCell7 = Sheet2.Range("AA4")
       
        LoB2_LastRow = Sht4.Cells(Sht4.Rows.Count, StartCell7.Column).End(xlUp).Row - 1
        
        LoB2.Resize LoB2.Range.Resize(LoB2_LastRow)
            
    Sht4.Range("AA2:AK2").Copy
    Sht4.Range("AA3:AK3").PasteSpecial Paste:=xlPasteValues
    
       Range("AK4").FormulaR1C1 = "=SUMIFS(C[-26],C[-36],RC[-10],C[-35],RC[-9],C[-34],RC[-8],C[-33],RC[-7],C[-32],RC[-6],C[-31],RC[-5],C[-30],RC[-4],C[-29],RC[-3],C[-28],RC[-2],C[-27],RC[-1])"
       Range("AK4").Select
       Selection.Copy
            Range(Selection, Selection.End(xlDown)).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Selection.Copy
            Sht4.Range("AK4").PasteSpecial Paste:=xlPasteValues
            Selection.NumberFormat = "0.0"
    
    Sheet2.Range("A1").Select

' Finding Last Row and Column and updating the data for("Sales_Data") worksheet

    Sheet5.Select

Set Sht2 = Sheet5
Set LoB = Sht2.ListObjects("Sales_OPs_Data_Table")
Set StartCell2 = Sheet5.Range("A3")

    Sht2.ListObjects("Sales_OPs_Data_Table").DataBodyRange.Delete
        
        LastRow3 = Sht2.Cells(Sht2.Rows.Count, StartCell2.Column).End(xlUp).Row
        LastColumn3 = Sht2.Cells(StartCell2.Row, Sht2.Columns.Count).End(xlToLeft).Column + 2
        
    Sht2.Range("A1:Q1").Copy
    Sht2.Range("A2:Q2").PasteSpecial Paste:=xlPasteValues
        
    Sht2.Range("A2:J" & LastRow3).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Sales_OPs_Data_Table[Business Function]"), Unique:=True
       
Set StartCell3 = Sheet5.Range("AA3")

        LoB_LastRow = Sht2.Cells(Sht2.Rows.Count, StartCell3.Column).End(xlUp).Row
        
        LoB.Resize LoB.Range.Resize(LoB_LastRow)
    
    Sht2.Range("AA1:BA1").Copy
    Sht2.Range("AA2:BA2").PasteSpecial Paste:=xlPasteValues
        
        Range("AK3").FormulaR1C1 = "=SUMIFS(C[-26],C[-36],[@[Business Function]],C[-35],[@[Industry Group]],C[-34],[@[Industry SubGroup]],C[-33],[@[Customer Group]],C[-32],[@[Resp. SubRegion 1]],C[-31],[@[Sales SubRegion 1]],C[-30],[@[Sales SubRegion 2]],C[-29],[@[Country (End Use)]],C[-28],[@[Sold-to party]],C[-27],[@AccM])"
        Range("AL3").FormulaR1C1 = "=SUMIFS(C[-26],C[-37],[@[Business Function]],C[-36],[@[Industry Group]],C[-35],[@[Industry SubGroup]],C[-34],[@[Customer Group]],C[-33],[@[Resp. SubRegion 1]],C[-32],[@[Sales SubRegion 1]],C[-31],[@[Sales SubRegion 2]],C[-30],[@[Country (End Use)]],C[-29],[@[Sold-to party]],C[-28],[@AccM])"
        Range("AM3").FormulaR1C1 = "=SUMIFS(C[-26],C[-38],[@[Business Function]],C[-37],[@[Industry Group]],C[-36],[@[Industry SubGroup]],C[-35],[@[Customer Group]],C[-34],[@[Resp. SubRegion 1]],C[-33],[@[Sales SubRegion 1]],C[-32],[@[Sales SubRegion 2]],C[-31],[@[Country (End Use)]],C[-30],[@[Sold-to party]],C[-29],[@AccM])"
        Range("AN3").FormulaR1C1 = "=SUMIFS(C[-26],C[-39],[@[Business Function]],C[-38],[@[Industry Group]],C[-37],[@[Industry SubGroup]],C[-36],[@[Customer Group]],C[-35],[@[Resp. SubRegion 1]],C[-34],[@[Sales SubRegion 1]],C[-33],[@[Sales SubRegion 2]],C[-32],[@[Country (End Use)]],C[-31],[@[Sold-to party]],C[-30],[@AccM])"
        Range("AO3").FormulaR1C1 = "=SUMIFS(C[-26],C[-40],[@[Business Function]],C[-39],[@[Industry Group]],C[-38],[@[Industry SubGroup]],C[-37],[@[Customer Group]],C[-36],[@[Resp. SubRegion 1]],C[-35],[@[Sales SubRegion 1]],C[-34],[@[Sales SubRegion 2]],C[-33],[@[Country (End Use)]],C[-32],[@[Sold-to party]],C[-31],[@AccM])"
        Range("AP3").FormulaR1C1 = "=SUMIFS(C[-26],C[-41],[@[Business Function]],C[-40],[@[Industry Group]],C[-39],[@[Industry SubGroup]],C[-38],[@[Customer Group]],C[-37],[@[Resp. SubRegion 1]],C[-36],[@[Sales SubRegion 1]],C[-35],[@[Sales SubRegion 2]],C[-34],[@[Country (End Use)]],C[-33],[@[Sold-to party]],C[-32],[@AccM])"
        Range("AQ3").FormulaR1C1 = "=SUMIFS(RE_Turnover!C[-32],RE_Turnover!C[-42],Sales_Data!RC[-16],RE_Turnover!C[-41],Sales_Data!RC[-15],RE_Turnover!C[-40],Sales_Data!RC[-14],RE_Turnover!C[-39],Sales_Data!RC[-13],RE_Turnover!C[-38],Sales_Data!RC[-12],RE_Turnover!C[-37],Sales_Data!RC[-11],RE_Turnover!C[-36],Sales_Data!RC[-10],RE_Turnover!C[-35],Sales_Data!RC[-9],RE_Turnover!C[-34],Sales_Data!RC[-8],RE_Turnover!C[-33],Sales_Data!RC[-7])"
        Range("AR3").FormulaR1C1 = "=SUMIFS(RE_LY!C[-7],RE_LY!C[-17],Sales_Data!RC[-17],RE_LY!C[-16],Sales_Data!RC[-16],RE_LY!C[-15],Sales_Data!RC[-15],RE_LY!C[-14],Sales_Data!RC[-14],RE_LY!C[-13],Sales_Data!RC[-13],RE_LY!C[-12],Sales_Data!RC[-12],RE_LY!C[-11],Sales_Data!RC[-11],RE_LY!C[-10],Sales_Data!RC[-10],RE_LY!C[-9],Sales_Data!RC[-9],RE_LY!C[-8],Sales_Data!RC[-8])"
        Range("AS3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13],Ops_Data_Backend_Calc!C[-38],Sales_Data!RC[-12],Ops_Data_Backend_Calc!C[-37],Sales_Data!RC[-11],Ops_Data_Backend_Calc!C[-36],Sales_Data!RC[-10],Ops_Data_Backend_Calc!C[-35],Sales_Data!RC[-9])"
        Range("AT3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13],Ops_Data_Backend_Calc!C[-38],Sales_Data!RC[-12],Ops_Data_Backend_Calc!C[-37],Sales_Data!RC[-11],Ops_Data_Backend_Calc!C[-36],Sales_Data!RC[-10])"
        Range("AU3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13],Ops_Data_Backend_Calc!C[-38],Sales_Data!RC[-12],Ops_Data_Backend_Calc!C[-37],Sales_Data!RC[-11])"
        Range("AV3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13],Ops_Data_Backend_Calc!C[-38],Sales_Data!RC[-12])"
        Range("AW3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14],Ops_Data_Backend_Calc!C[-39],Sales_Data!RC[-13])"
        Range("AX3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-49],Sales_Data!RC[-23],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15],Ops_Data_Backend_Calc!C[-40],Sales_Data!RC[-14])"
        Range("AY3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-50],Sales_Data!RC[-24],Ops_Data_Backend_Calc!C[-49],Sales_Data!RC[-23],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16],Ops_Data_Backend_Calc!C[-41],Sales_Data!RC[-15])"
        Range("AZ3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-51],Sales_Data!RC[-25],Ops_Data_Backend_Calc!C[-50],Sales_Data!RC[-24],Ops_Data_Backend_Calc!C[-49],Sales_Data!RC[-23],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17],Ops_Data_Backend_Calc!C[-42],Sales_Data!RC[-16])"
        Range("BA3").FormulaR1C1 = "=SUMIFS(Ops_Data_Backend_Calc!C[-34],Ops_Data_Backend_Calc!C[-52],Sales_Data!RC[-26],Ops_Data_Backend_Calc!C[-51],Sales_Data!RC[-25],Ops_Data_Backend_Calc!C[-50],Sales_Data!RC[-24],Ops_Data_Backend_Calc!C[-49],Sales_Data!RC[-23],Ops_Data_Backend_Calc!C[-48],Sales_Data!RC[-22],Ops_Data_Backend_Calc!C[-47],Sales_Data!RC[-21],Ops_Data_Backend_Calc!C[-46],Sales_Data!RC[-20],Ops_Data_Backend_Calc!C[-45],Sales_Data!RC[-19],Ops_Data_Backend_Calc!C[-44],Sales_Data!RC[-18],Ops_Data_Backend_Calc!C[-43],Sales_Data!RC[-17])"

        Range("AK3:BA3").Select
        Selection.Copy
        Range("AK3").Select
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
              Application.CutCopyMode = False
        Selection.Copy
        Sht2.Range("AK3").PasteSpecial Paste:=xlPasteValues
        
        LoB_LastRow = Sht2.Cells(Sht2.Rows.Count, StartCell3.Column).End(xlUp).Row
        Range("AK3:BA" & LoB_LastRow).Select
        Selection.NumberFormat = "0.0"
    
    Sheet5.Range("A1").Select

' Finding Last Row and Column and updating the data for("Threats_Data") worksheet

    Sheet9.Select

Set Sht4 = Sheet9
Set LoB3 = Sht4.ListObjects("Threats_Data_Table")
Set StartCell8 = Sheet9.Range("A3")

    Sht4.ListObjects("Threats_Data_Table").DataBodyRange.Delete
        
        LastRow6 = Sht4.Cells(Sht4.Rows.Count, StartCell8.Column).End(xlUp).Row
        LastColumn6 = Sht4.Cells(StartCell8.Row, Sht4.Columns.Count).End(xlToLeft).Column
        
    Sht4.Range("A1:Y1").Copy
    Sht4.Range("A2:Y2").PasteSpecial Paste:=xlPasteValues
        
    Sht4.Range("A2:Y" & LastRow6).Copy Range("AA2")

Set StartCell9 = Sheet11.Range("AA3")
       
        LoB3_LastRow = Sht4.Cells(Sht4.Rows.Count, StartCell9.Column).End(xlUp).Row
        
'        LoB3.Resize LoB3.Range.Resize(LoB3_LastRow)
            
    Sht4.Range("AA1:AY1").Copy
    Sht4.Range("AA2:AK2").PasteSpecial Paste:=xlPasteValues
    
       Range("AU3:AY" & LoB3_LastRow).Select
    
       Selection.NumberFormat = "0.0"
       Range("A1").Select
    
' Replacing the "# and ## to "# - Not assigned"

MyArray = Array("OPs_Model_Data", "OPs_Data", "Sales_Data", "Threats_Data", "RE_Turnover", "RE_LY")

    For Each i In MyArray
        Sheets(i).Activate
        Sheets(i).Range("A1").Select
            
        Cells.Replace What:="##", Replacement:="# - Not assigned", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            
        Cells.Replace What:="#", Replacement:="# - Not assigned", LookAt:=xlWhole _
        , SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        
    Next i
    
    Sheet6.Select
    Range("A1").Select
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True

'ActiveWorkbook.RefreshAll

' Refreshing other queries

        ActiveWorkbook.Connections("Query - Sales_OPs_Data_Table").Refresh
        ActiveWorkbook.Connections("Query - Threats_Data_Table").Refresh

Sheet2.Visible = xlSheetHidden
Sheet4.Visible = xlSheetVeryHidden
Sheet5.Visible = xlSheetHidden
Sheet9.Visible = xlSheetHidden
Sheet12.Visible = xlSheetHidden
Sheet10.Visible = xlSheetVeryHidden
Sheet11.Visible = xlSheetHidden
Sheet16.Visible = xlSheetVeryHidden
    
End Sub


Public Sub Enable_Analysis_For_Office()

    Dim addin As COMAddIn
    On Error GoTo AAO_Addin_Err
    Dim AddInnStatus As Boolean
    AddInnStatus = False
    
    ''checks if AAO add-in has been installed or not
    For Each addin In ThisWorkbook.Application.COMAddIns
        If addin.progID = "SapExcelAddIn" Then
            addin.Connect = True
            AddInnStatus = True
        End If
    Next
    
AAO_Addin_Err:
    If AddInnStatus <> True Then
        MsgBox "Please install AAO Add-in and re-run the tool", vbCritical, "Missing AAO Add-in"
        Exit Sub
    End If
End Sub

Public Sub Refresh_AAO_Queries()

    lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1")
    lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_2")
    lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_3")
    lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_5")
    lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_6")
If lResult = False Then
    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")
    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_2")
    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_3")
    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_5")
    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_6")
' Example: You can also skip the else
Else
    lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_1")
    lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_2")
    lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_3")
    lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_5")
    lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_6")

End If

End Sub

Respostas

5 SiddharthRout Dec 14 2020 at 15:15

Eu normalmente não teria respondido a este post, mas continuo me encontrando com esse tipo de pergunta, então vou compartilhar como lido com isso, esperando que isso ajude futuros visitantes também. No entanto, observe que este é o meu estilo de codificação pessoal e pode não ser o estilo de codificação comum aceitável. Sinta-se à vontade para escolher o que achar melhor.

1. Use Option Explicit

2. No VBA (ao contrário, digamos, no Vb.net) ao declarar variáveis, declare todas elas com o tipo correto, caso contrário, elas serão declaradas como a Variant. As variantes são mais lentas que os tipos nativos. Você pode querer ver O uso de variantes em vba é ruim para o desempenho? . Aqui está um exemplo

   Dim Sht, Sht1, Sht2, Sht3, Sht4, Sht5 As Worksheet

Acima, apenas Sht5é declarado como Worksheete Sht, Sht1, Sht2, Sht3, Sht4são declarados como Variants.

3. Sempre que você estiver trabalhando com eventos ...

Armazene as configurações padrão para que você possa reverter a elas no final da codificação.
Use o tratamento de erros para que você possa redefinir as configurações originais. Sempre que você estiver ligando / desligando eventos , use o tratamento de erros para ligá-los / desligá-los novamente, caso contrário, eles não serão reiniciados.

Aqui está um exemplo

    Option Explicit
    
    Sub Sample()
        Dim scrnUpdating As Boolean
        Dim dsplyAlerts As Boolean
        
        On Error GoTo Whoa
        
        With Application
            '~~> Get user's current setting
            scrnUpdating = .ScreenUpdating
            dsplyAlerts = .DisplayAlerts
            
            '~~> Set it to necessary setting
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With       
       
        '~~> Rest of your code

    LetsContinue:
        With Application
            '~~> Reset original settings
            .ScreenUpdating = scrnUpdating
            .DisplayAlerts = dsplyAlerts
        End With
        
        Exit Sub
    Whoa:
        MsgBox Err.Description
        Resume LetsContinue
    End Sub

4. Não crie desnecessariamente tantas variáveis. Reutilize-os sempre que possível . Veja o ponto 7 abaixo.

5. Como evitar o uso de Select no Excel VBA . Isso também vale para o seu caso. Veja este exemplo

Seu código

    MyArray = Array("Sales_Data", "Threats_Data", "RE_Turnover", "RE_LY")

    For Each i In MyArray
        If Sheets(i).Name <> Sheets("Sales_Data").Name Then
            '
            '
            '
        Else
            '
            '
            '
        End If
    Next i

também pode ser escrito como

    Dim SheetsArray, sht
    
    SheetsArray = Array("Sales_Data", "Threats_Data", "RE_Turnover", "RE_LY")
    
    Dim lRow As Long
    Dim rng As Range
    
    For Each sht In SheetsArray
        With Sheets(sht)
            '~~> Remove any filters
            .AutoFilterMode = False
                
            lRow = .Range("A" & .Rows.Count).End(xlUp).Row
            
            If .Name <> "Sales_Data" Then
                Set rng = .Range("A2:K" & lRow)
            Else
                Set rng = .Range("A2:Q" & lRow)
            End If
            
            With rng
                .AutoFilter Field:=4, Criteria1:="=Teshima", Operator:=xlOr, Criteria2:="=Teshima?Kaken"
            End With
            
            With .Columns(4).SpecialCells(xlCellTypeVisible)
                .Replace What:="Andhra?Pradesh", Replacement:="Andhra Pradesh", _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
                False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            End With
            
            '~~> Remove any filters
            .AutoFilterMode = False
        End With
    Next sht

Da mesma forma, seu código

    Set Sht = Sheet12
    Set Sht1 = Sheet4
    Set StartCell0 = Sheet12.Range("A3")
    Set StartCell1 = Sheet4.Range("A2")

    Sheet4.Select
    '
    '
    '        
    Worksheets("OPs_Model_Data").Range("A2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    Sheet12.Range("A1").Select

pode ser escrito como

    Dim wsA As Worksheet, wsB As Worksheet, wsDest As Worksheet
    Dim LastRow As Long, LastCol As Long
    
    Set wsA = Sheet12: Set wsB = Sheet4
    Set wsDest = ThisWorkbook.Worksheets("OPs_Model_Data")
    
    LastRow = wsB.Range("A" & wsB.Rows.Count).End(xlUp).Row
    wsB.Rows("2:" & wsBLastRow).Delete
    
    With wsA
        '~~> Reuse the lastrow variable
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        LastColumn = .Cells(3, .Columns.Count).End(xlToLeft).Column
        
        .Range(.Cells(3, 1), .Cells(LastRow, LastColumn)).Copy
        DoEvents
        
        wsDest.Range("A2").PasteSpecial Paste:=xlPasteValues
    End With

Basta replicar para Sheet11e Sheet2. Não há necessidade de usar .Select.

6. Comente seu código o máximo que puder para que possa entendê-lo quando examiná-lo, mesmo depois de 6 meses.

7. Para inserir uma fórmula em um intervalo, você não precisa inserir a fórmula em 1 célula e depois copiá-la e colá-la. Você pode inserir a fórmula em todo o intervalo de uma vez. Por exemplo, consulte

Range("AK3").FormulaR1C1 = "=SUMIFS(C[-26],C[-36],[@[Business Function]],C[-35],[@[Industry Group]],C[-34],[@[Industry SubGroup]],C[-33],[@[Customer Group]],C[-32],[@[Resp. SubRegion 1]],C[-31],[@[Sales SubRegion 1]],C[-30],[@[Sales SubRegion 2]],C[-29],[@[Country (End Use)]],C[-28],[@[Sold-to party]],C[-27],[@AccM])"
'
'
'

Vou explicar o acima usando um exemplo simples. Digamos que você queira introduzir a fórmula =Sum(A3:L3)na AK3e querem a mesma fórmula para ser colado para baixo a última linha, então esta pode ser escrita como