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").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" <(E-Mail Removed)> skrev i meddelelsen
news:023F78AF-28BC-415E-8651-(E-Mail Removed)...
> 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").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" <(E-Mail Removed)> skrev i meddelelsen
> news:023F78AF-28BC-415E-8651-(E-Mail Removed)...
> > 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").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
>
> .
>