Optimiser le code VBA et améliorer les performances

Dec 14 2020

J'ai développé une macro VBA qui est utilisée pour actualiser les 5 requêtes SAP AAO, puis copiez ces données de requêtes dans des tables en supprimant les doublons, puis chargées dans la requête d'alimentation. Là, j'ajoute des colonnes calculées, puis je charge ces données dans les pivots d'alimentation.

J'ai également utilisé le code VBA mentionné ci-dessous pour effectuer certaines actions dans chacune des feuilles, mais il faut actuellement plus de 4 minutes pour exécuter le code.

Au cours de ces 4 minutes, au moins 45 secondes sont consacrées à l'actualisation des requêtes AAO et à l'exécution de la macro de temps restant (y compris l'actualisation des requêtes d'alimentation)

J'apprends toujours VBA et j'ai donc utilisé le code de l'enregistreur de macros et aussi des select / activatefeuilles dans mon code. Je sais que ce n'est pas la bonne voie car je ne connais pas la meilleure option à part celle-ci. À cause de ces choses, la macro prend plus de temps à s'exécuter.

Si je n'utilise pas de selectcode pour sélectionner les feuilles, alors je reçois un message d'erreur et le code ne s'exécute pas.

Ce serait formidable si quelqu'un pouvait m'aider à optimiser ce code, de sorte qu'il faudra moins de temps pour effectuer des opérations 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

Réponses

5 SiddharthRout Dec 14 2020 at 15:15

En général, je n'aurais pas répondu à ce message, mais je continue de rencontrer ce genre de questions, je vais donc partager comment je le gère en espérant que cela aidera également les futurs visiteurs. Cependant, veuillez noter que ceci est mon style de codage personnel et peut ne pas être le style de codage commun acceptable. N'hésitez pas à choisir ce que vous pensez être le mieux.

1. Utilisez l' option Explicit

2. Dans VBA (contrairement à Vb.net) lors de la déclaration de variables, déclarez-les toutes avec le bon type sinon elles seront déclarées comme a Variant. Les variantes sont plus lentes que les types natifs. Vous voudrez peut-être voir L'utilisation de variantes dans vba est-elle mauvaise pour les performances? . Voici un exemple

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

Dans ce qui précède, seul Sht5est déclaré comme Worksheetet Sht, Sht1, Sht2, Sht3, Sht4est déclaré comme Variants.

3. Chaque fois que vous travaillez avec des événements ...

Stockez les paramètres par défaut afin de pouvoir y revenir à la fin du codage.
Utilisez la gestion des erreurs afin de pouvoir réinitialiser les paramètres d'origine. Chaque fois que vous activez / désactivez des événements , utilisez la gestion des erreurs pour les réactiver / désactiver, sinon ils ne seront pas réinitialisés.

Voici un exemple

    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. Ne créez pas inutilement autant de variables. Réutilisez-les autant que possible . Voir le point 7 ci-dessous.

5. Comment éviter d'utiliser Select dans Excel VBA . Cela vaut également pour votre cas. Voir cet exemple

Votre code

    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

peut également être écrit comme

    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

De même votre code

    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

peut être écrit comme

    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

Reproduisez simplement ceci pour Sheet11et Sheet2. Pas besoin d'utiliser .Select.

6. Commentez votre code autant que vous le pouvez afin que vous puissiez comprendre votre code lorsque vous le regardez même après 6 mois.

7. Pour entrer une formule dans une plage, il n'est pas nécessaire de saisir la formule dans 1 cellule, puis de la copier et de la coller. Vous pouvez saisir la formule dans toute la plage en une seule fois. Par exemple, reportez-vous à

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])"
'
'
'

Je vais expliquer ce qui précède à l'aide d'un exemple simple. Dites que vous voulez entrer la formule =Sum(A3:L3)dans AK3et que vous voulez la même formule à coller jusqu'à la dernière ligne, cela peut être écrit