Automation Error. What is this?

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I keep getting this error messgae at the end of my macro. The macro runs
fine; except this error message pops up at the end and I have to click the
"End" button. I need to know how to get rid of it.
The macro is run from a button,cmdgetData, on the sheet called
Worksheets("BO Download"):

Private Sub cmdgetData_Click()
Dim f, ws As Worksheet, Msg
Dim BO_Datafile_Name As String, BOReport_lastColumn As String
Dim BOReport_lastRow As Long, BOPos As Integer
Dim BOReportWS As Worksheet
Dim rngBOReport As Range, c As Range

With Application
.ScreenUpdating = True
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
'" THIS SECTION OF CODE RETRIVES THE BO FILES FROM THE HARD DRIVE "'
'" "'
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
f = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If f = False Then
Msg = MsgBox(prompt:="Locate the BO Milestones File",
Buttons:=vbApplicationModal + _
vbOKOnly + vbDefaultButton1 + vbInformation, Title:="Daily BO
Milestones Download not Opened")

If Msg = vbOK Then ' User choose Ok.
Msg = Msg & "Cluster Selection Required"
End If
End If

'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
'"" THIS SECTION OF CODE OPENS THE BO FILES FROM THE HARD DRIVE ""'
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
If f <> "" Then
Workbooks.Open filename:=f, UpdateLinks:=0, ReadOnly:=True
BO_Datafile_Name = ActiveWorkbook.Name
Else
Worksheets("BO Download").Visible = True
Range("A4").Select
Exit Sub
End If

'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
'"" THIS SECTION OF CODE COPIES THE BO SHEET FROM THE BO FILE ""'
'"" AND PASTE IT INTO THE SUMMARY REPORT FILE ""'
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
Workbooks(BO_Datafile_Name).Worksheets("Report 1").Select
Workbooks(BO_Datafile_Name).Worksheets("Report 1").Copy
Before:=Workbooks("HSPA Homer Task Report.xls").ActiveSheet

'"" CLOSE THE BO DOWNLOAD DATA FILE ""'
Workbooks(BO_Datafile_Name).Activate
Workbooks(BO_Datafile_Name).Close

Workbooks("HSPA Homer Task Report.xls").Worksheets("Report 1").Activate
BOReport_lastRow = ActiveSheet.UsedRange.Rows.Count

Set BOReportWS = Worksheets("BO Download")

ThisWorkbook.Worksheets("Report 1").Range("B5:L" & BOReport_lastRow).Copy
ThisWorkbook.Worksheets("BO Download").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("B2").Select
ThisWorkbook.Worksheets("Report 1").Delete
Columns("D:D").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'BOReport_lastRow = BOReportWS.Range("A65536").End(xlUp).Row

Call sortSITELIST
Call filterSITELIST
Call getTASKSTATUS
Call defineRANGES
Call buildDDS_SITELIST

Dim filename As String
filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4)
Worksheets("BO Download").Select
Worksheets("BO Download").Delete
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

ActiveWorkbook.SaveAs filename & "_" & Format(Date, "mmmdd_yy") & ".xls"
End Sub
 
Hi

In the last section of the code you use 'filename' as variable, which may
cause the error, as it is a VBA keyword. You should never use VBA keywords
as variable names, as it often mean troubles.

Try to use another variable name as 'myFileName' or 'fName'.

Regards,
Per
 
I think I might have found the problem, this line:
Worksheets("BO Download").Delete
This worksheet contains the button the code is being run from. I am deleting
the worksheet while the code is still running. When l move this line of code
out of this subroutine, everything works fine.
 
Back
Top