Macro runs fine via button click event but stops via using Macro shortcut

[pizzaboy]

As per title...

What I am trying to do is copy a worksheet from another file.

I've stripped all of the variable declarations and all other non-relevant code in the example below, just know that it does work via the button click event.

When I run the code via pressing the Macro shortcut, the file opens successfully but the code just stops running. No errors. No nothing.

I put a breakpoint where it actually performs the copy, but it never gets there?

That pathetic looking DoEvents was very hopeful, thinking that maybe the file opened too slowly...

Curiously... running the macro directly from the Macros panel, I get a "Run-time error '9': Subscript out of range" error where I try to close Window(file).Close. The file appeared to have automatically been closed?! Commenting out that line and the code ran as it should...

Option Explicit


Private Sub btnQuotes_Refresh_Click()
    Call Quotes_Refresh
End Sub

Public Sub Quotes_Refresh()
'
' Keyboard Shortcut: Ctrl+Shift+U
'
    Workbooks.Open ("" & QUOTES_DIRECTORY & file & "")
    DoEvents


    
    Sheets(ws).Move After:=Workbooks("SE.xlsm").Sheets(position)



    Windows(file).Close
End Sub

Any help would be very much appreciated.

Thanks.

 

[rlv01]

When I run the code via pressing the Macro shortcut, the file opens successfully but the code just stops running. No errors. No nothing.

Weird behavior, but I was able to get it to run reliably from the shortcut by putting the DoEvents before the workbook open statement.

Curiously... running the macro directly from the Macros panel, I get a "Run-time error '9': Subscript out of range" error where I try to close Window(file).Close.

That's not how you close an open workbook. See below for the correct method.

Public Sub Quotes_Refresh()
'
' Keyboard Shortcut: Ctrl+Shift+U
'
    Dim WB As Workbook

    DoEvents
    Set WB = Workbooks.Open(QUOTES_DIRECTORY & file)

    Sheets(WS).Move After:=Workbooks("SE.xlsm").Sheets(Position)
    WB.Close False
End Sub

 

[Norie]

Create a reference to the workbook you are opening and use that in the subsequent code to copy from and to close the workbook.

Option Explicit


Private Sub btnQuotes_Refresh_Click()
    Call Quotes_Refresh
End Sub

Public Sub Quotes_Refresh()
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Dim wbQuote As Workbook

    Set wbQuote = Workbooks.Open(QUOTES_DIRECTORY & file)
    
    wbQuotes.Sheets(ws).Move After:=Workbooks("SE.xlsm").Sheets(position)

    wbQuote.Close False

End Sub

 

[pizzaboy]

Thanks rlv01!

I used your initialization code to reference the workbook, though the file is automatically getting closed. I got an automation error so I had to comment it out...

Putting the DoEvents above the open call strangely solved the issue!

Not sure if its because I ran the macro on a different installation of Excel (another pc), as I tried uninstalling Excel from my main machine due to the automation error and now its refusing to successfully install