Hi there!!
I've built a macro to open a file and copy one of its worksheets into a different file (both files are in Excel 97-2003 format). The macro works great when I manually select the macro from the macros dialog box and click "Run." However, I tried to assign the macro to a hotkey. Now when I click the hotkey (Ctrl+Shift+whatever...), all the macro will do is open the file - it does not copy the worksheet into the different file. I *don't* receive an error message. The macro just opens the file, and then ends.</SPAN>
Here’s my code so far:</SPAN>
Sub Get_MidMonthFile()</SPAN>
Dim StrFile As String</SPAN>
Set wbSection = ActiveWorkbook</SPAN>
Set wsSection = wbSection.ActiveSheet</SPAN>
xdir = wbSection.Path</SPAN>
Application.DisplayAlerts = False</SPAN>
On Error GoTo 0</SPAN>
Application.DisplayAlerts = True</SPAN>
StrFile = xdir + "\" + "Monthly File.xls"</SPAN>
If Dir(StrFile) = "" Then</SPAN>
MsgBox "No File Found: Monthly File.xls"</SPAN>
Else</SPAN>
Workbooks.Open Filename:=StrFile</SPAN>
End If</SPAN>
wbSection.Activate </SPAN>‘<------</SPAN></SPAN>Using the hotkey, the code macro stops here. Without the hotkey, the macro continues to run to completion.</SPAN>
Windows("Monthly File.xls"). _</SPAN>
Activate</SPAN>
Sheets("monthly sheet").Select</SPAN>
Sheets("monthly sheet").Copy Before:=Workbooks( _</SPAN>
"testing.xls").Sheets(2)</SPAN></SPAN>
I've built a macro to open a file and copy one of its worksheets into a different file (both files are in Excel 97-2003 format). The macro works great when I manually select the macro from the macros dialog box and click "Run." However, I tried to assign the macro to a hotkey. Now when I click the hotkey (Ctrl+Shift+whatever...), all the macro will do is open the file - it does not copy the worksheet into the different file. I *don't* receive an error message. The macro just opens the file, and then ends.</SPAN>
Here’s my code so far:</SPAN>
Sub Get_MidMonthFile()</SPAN>
Dim StrFile As String</SPAN>
Set wbSection = ActiveWorkbook</SPAN>
Set wsSection = wbSection.ActiveSheet</SPAN>
xdir = wbSection.Path</SPAN>
Application.DisplayAlerts = False</SPAN>
On Error GoTo 0</SPAN>
Application.DisplayAlerts = True</SPAN>
StrFile = xdir + "\" + "Monthly File.xls"</SPAN>
If Dir(StrFile) = "" Then</SPAN>
MsgBox "No File Found: Monthly File.xls"</SPAN>
Else</SPAN>
Workbooks.Open Filename:=StrFile</SPAN>
End If</SPAN>
wbSection.Activate </SPAN>‘<------</SPAN></SPAN>Using the hotkey, the code macro stops here. Without the hotkey, the macro continues to run to completion.</SPAN>
Windows("Monthly File.xls"). _</SPAN>
Activate</SPAN>
Sheets("monthly sheet").Select</SPAN>
Sheets("monthly sheet").Copy Before:=Workbooks( _</SPAN>
"testing.xls").Sheets(2)</SPAN></SPAN>