Hi Team,
I had written a VBA code, which ran when ran it step by step. But it crashes when run directly. So I broke down it into steps to check where the error is.
Pasting below the code which crashes when run directly and works fine when run step by step. Can anyone please help me what's wrong:
Note: I've already declared the variables and objects in Step1.
Code
Sub Step2()
macro_wb.Worksheets("Acc_Mt_Data").Activate
Range("B2:B" & lr).Select
Selection.NumberFormat = "d-mmm-yy"
Range("A1").Value = "ACCOUNT_NUMBER"
Range("B1").Value = "ACCOUNT_OPENING_DATE"
macro_wb.Worksheets("Dashboard_Data").Activate
Range("A1").Value = "ACCOUNT_NUMBER"
Range("B1").Value = "PHYSICAL_FILE_RECEIVING_DATE"
Range("C1").Value = "OBS NUMBER"
Range("D1").Value = "CUSTOMER NAME"
Range("E1").Value = "RIM NUMBER"
For i = 1 To db_count
Workbooks.Open Filename:=dashboards_path(i) & dashboards(i), UpdateLinks:=False, ReadOnly:=True
'Range("").Select
Worksheets(dashboard_sheets(i)).Activate
ActiveSheet.AutoFilterMode = False
Call last_row
If dashboards(i) = "GENM DASHBOARD.xlsx" Then
Range("1:1").Select
Selection.AutoFilter Field:=10, Criteria1:="=OPENED" _
, Operator:=xlAnd
Selection.AutoFilter Field:=32, Criteria1:="<>" _
, Operator:=xlAnd
Sheets(dashboard_sheets(i)).Range("H2:H" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Activate
Call last_row
lr1 = lr
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 1).PasteSpecial Paste:=xlPasteValues
Workbooks(dashboards(i)).Activate
Call last_row
Sheets(dashboard_sheets(i)).Range("H2:H" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 2).PasteSpecial Paste:=xlPasteValues
Sheets(dashboard_sheets(i)).Range("B2:B" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 3).PasteSpecial Paste:=xlPasteValues
Sheets(dashboard_sheets(i)).Range("F2:F" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 4).PasteSpecial Paste:=xlPasteValues
Sheets(dashboard_sheets(i)).Range("G2:G" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 5).PasteSpecial Paste:=xlPasteValues
Else
Range("1:1").Select
Selection.AutoFilter Field:=13, Criteria1:="=OPENED" _
, Operator:=xlAnd
Selection.AutoFilter Field:=27, Criteria1:="<>" _
, Operator:=xlAnd
Sheets(dashboard_sheets(i)).Range("I2:I" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Activate
Call last_row
lr1 = lr
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 1).PasteSpecial Paste:=xlPasteValues
Workbooks(dashboards(i)).Activate
Call last_row
Sheets(dashboard_sheets(i)).Range("AA2:AA" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 2).PasteSpecial Paste:=xlPasteValues
Sheets(dashboard_sheets(i)).Range("D2:D" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 3).PasteSpecial Paste:=xlPasteValues
Sheets(dashboard_sheets(i)).Range("F2:F" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 4).PasteSpecial Paste:=xlPasteValues
Sheets(dashboard_sheets(i)).Range("H2:H" & lr).SpecialCells(xlCellTypeVisible).Copy
macro_wb.Worksheets("Dashboard_Data").Cells(lr1 + 1, 5).PasteSpecial Paste:=xlPasteValues
End If
Application.CutCopyMode = False
Workbooks(dashboards(i)).Activate
ActiveWorkbook.Close SaveChanges:=False
Next
Worksheets("Summary").Activate
Range("A1").Select
End Sub
Display More