Hi Dave:
I scoured the internet for a solution, and I found something I posted
back in 2008:
"When the shift key is part of the hotkey sequence used to
call any macro which has a Workbooks.Open command, the Workbooks.Open
command is killed, and execution of the macro is stopped. The infuriating
thing is that Microsoft has known about this problem since about the year
2000, and, apparently hasn't come up with a fix yet--All versions from
Microsoft Excel 2000 up to the present are affected. (See Knowledge Base
article ID 555263, last reviewed by Microsoft on 2/26/05.) The workaround: Do
not use the shift key in any hotkey used to call any Excel macro which
contains a Workbooks.Open command. "

Here is somthing Dave Peterson posted on Wednesday, October 01, 2008 9:09 AM:

"Remove the shift key from the hotkey assignment.

Holding down the shiftkey while you're opening a file tells excel not to run
the
workbook_open event or the auto_open procedure. And it confuses excel so that
it never goes back to finish your code."

Thanks! And may you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"Dave Peterson" wrote:

It's the shift key.

If you have a workbook that has a workbook_open event or an auto_open procedure,
then if you open that file with the shift key held down, you stop those
procedures from running.

The shift-key in the shortcut combination confuses excel/vba to stop after you
open a file using one of those shortcut keys.

MichaelDavid wrote:

Hi Jacob:
After I made a minor change (corrected the file name as follows:
strFolder = "C:\InsiderIndustryResults\BookIssuers"), your suggested macro
ran perfectly from the VBE Editor. But when I assigned Ctrl+Shift+K hotkey to
it, it died after opening the file DataFileToOpen. So it exhibits the same
behavior as the way I originally coded the macro.
Next, I changed the hotkey from Ctrl+Shift+K to Ctrl+o, and the macro ran
perfectly. A later suggestion in this thread suggested removing the Shift
from the hotkey. At this moment, as I see it, either Shift is causing the
problem, or I have exceeded the max number of hotkeys allowed. May you have a
blessed day.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

"Jacob Skaria" wrote:

Try

Sub TestFileOpen()
Dim CurrentActiveSheet As String
Dim DataFileToOpen As String
Dim strFolder As String
Dim wb As Workbook

strFolder = "C:\InsiderIndustryResults\BookIssuers\"
CurrentActiveSheet = ActiveSheet.Name


If Dir(strFolder & CurrentActiveSheet & ".xlsm") < "" Then
DataFileToOpen = strFolder & CurrentActiveSheet & ".xlsm"
MsgBox "DataFileToOpen is " & DataFileToOpen
Set wb = Workbooks.Open(Filename:=DataFileToOpen)
wb.Worksheets(CurrentActiveSheet).Range("AJ7:AU140 ").Copy _
Workbooks("BookIssuers.xlsm").Worksheets(CurrentAc tiveSheet).Range("AJ7")
wb.Close
End If

End Sub

--
Jacob


"MichaelDavid" wrote:

Greetings! The below macro runs perfectly from within the VBE
Editor(RunSub/UserForm), but when run via a hotkey (Ctrl+Shift+K), Excel
gives up after executing the following instruction:
Workbooks.Open FileName:=DataFileToOpen .
That file opens properly but Excel does not execute the rest of the
instructions in the macro. The program doesn't hang, and there are no error
messages.

Sub TestFileOpen()

' Hotkey Ctrl+Shift+K

' Note: ActiveSheet.Name is 19TwinButte
' Note: DataFileToOpen is
"C:\InsiderIndustryResults\BookIssuers19TwinButte. xlsm"
' and it exists.

Dim CurrentActiveSheet As String
Dim DataFileToOpen As String

CurrentActiveSheet = ActiveSheet.Name

' The sheet name is used in forming the file name. Thus the sheet name is
19TwinButte, and the file name is "BookIssuers19TwinButte.xlsm" .
19TwinButte is the name of one of the worksheets in file
"BookIssuers19TwinButte.xlsm"

If Dir("C:\InsiderIndustryResults\BookIssuers" & CurrentActiveSheet &
".xlsm") < "" Then
DataFileToOpen = "C:\InsiderIndustryResults\BookIssuers" &
CurrentActiveSheet & ".xlsm"
MsgBox "DataFileToOpen is " & DataFileToOpen
Workbooks.Open FileName:=DataFileToOpen
Workbooks("BookIssuers" & CurrentActiveSheet & ".xlsm").
Worksheets(CurrentActiveSheet).Activate
' Copy data from that workbook into active workbook:
Workbooks("BookIssuers" & CurrentActiveSheet &
".xlsm").Worksheets(CurrentActiveSheet).Range("AJ7 :AU140").Copy
Workbooks("BookIssuers.xlsm").Worksheets(CurrentAc tiveSheet).Range("AJ7")

' Now close that workbook:
ActiveWorkbook.Close
End If

End Sub

I will greatly appreciate any suggestions or workarounds.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

--

Dave Peterson
.