0

I'm working with some downloaded reports from SAP.

I need to filter this reports by one column and copy this filtered report to another file in my work share drive.

Sometimes when I tried to run this macro bellow at my production server, the excel gets freeze for hours (4 hours or more), then I need to stop everything and start it again.

Do you know what can I do to "clean" the excel before run this macro? Maybe something to clear cache, or somenthing in the code?


Sub PasteReportZFIInflowCheck()

' This macro will open the downloaded ZFI report from SAP, filter to only InflowCheck and copy to the final report at Sharedrive


    ' Disable Alerts, ScreenUpdating
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    

    ' Set Variables
    Try = 0
    Set ReportPath = Range("A1")
    Set Report = Range("B1")
    Set ResultPath = Range("A2")
    Set Result = Range("B2")
    Set PasteCell = Range("A3")
    Set SheetName = Range("C2")
    Dim wb As Workbook
    
    
' Main Task
MainTask:
    
    Do While Try < 3
    On Error GoTo ErrorHandler
    
    ' SAP Report Download (Filter and Copy)
    Workbooks.Open Filename:=ReportPath
    
    'Replace . to ,
    Columns("AH:BB").Select
    Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    'Filter
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$BC$999999").AutoFilter Field:=1, Criteria1:="1"
         
    'Check if we have any Data
    i = 1
    j = 1
    Do While Sheets("Sheet1").Cells(i, j) <> Empty
    i = i + 1
    Cells(i, j).Select
    Value = ActiveCell
        If Value = "1" Then
        CheckEmpty = 1
        End If
    Loop
    
    'Have data
    If CheckEmpty = 1 Then
    
    ' Copy all data
    Range("A1").Select
    Selection.Offset(1, 0).Select ' seta para baixo
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
        
    
    ' Open Result sheet
    Workbooks.Open Filename:=ResultPath
    ActiveWorkbook.Worksheets("Check 01 a 31_SAP").Activate
       
    
    'Look for last empty line
    Range(PasteCell).Select
    i = 2
    j = ActiveCell.Column
    Do While Sheets("Check 01 a 31_SAP").Cells(i, j) <> Empty
    i = i + 1
    Loop
    Cells(i, j).Select

    'Paste the report
    ActiveSheet.Paste
    
    End If
    

    'Save and close Result workbook
    ActiveWorkbook.Close SaveChanges:=True
    

    'Close others workbooks
    ActiveWorkbook.Worksheets("Sheet1").Activate
    ActiveWorkbook.Close SaveChanges:=False
    
    'Cancel Loop
    Try = 3
    Loop
    
    ' Clear Copied data
    Application.CutCopyMode = False
    Application.CutCopyMode = True
        

    ' Enable ScreenUpdating
     Application.ScreenUpdating = True
    
    
    Application.Quit
    
    Exit Sub
    
    
ErrorHandler:
    On Error GoTo -1
    Try = Try + 1
    
    ' close all workbooks
    ActiveWorkbook.Worksheets("Check 01 a 31_SAP").Activate
    ActiveWorkbook.Close SaveChanges:=False
    ActiveWorkbook.Worksheets("Sheet1").Activate
    ActiveWorkbook.Close SaveChanges:=False
    
    GoTo MainTask
    
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • This macro will run sometimes per day (60~100) – Caio Pucci May 23 '22 at 13:20
  • First step would be [to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Select makes everything totally slow. Also avoid `.Activate` (use same technique). Also using autofilter can make your code really slow (depending on the amount of data) especially when you perform it in a loop. – Pᴇʜ May 23 '22 at 14:04

0 Answers0