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
Befo=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

"Ayo" skrev i meddelelsen
...
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
Befo=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

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.


"Per Jessen" wrote:

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

"Ayo" skrev i meddelelsen
...
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
Befo=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

.