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