Cant copy/paste from file when using shortcut/hot key

bkahn

New Member
Joined
Oct 19, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
I have encountered a very strange problem. I have some code that opens a second workbook, copies a sheet from the second workbook into the first workbook (where the VBA is), then closes the second workbook. It works fine if I run it from the code window, or by using View Macros. However, if I try to run it using an assigned Shortcut/Hot Key (CTRL+J ir CTRK+M), it will open the second workbook then hang. It won't do the copy or close the workbook. I have tried 4 different ways of doing the copy/paste (cells.select, selection.copy, ActiveSheet.copy, and the following code). All of them behave the same way - they work but not with the shortcut/hot key

SourcePath = "C:\Users\bkahn\Downloads\Duplicates\"
Filename = "500GB.csv"
SourceFile = SourcePath & Filename

Set SourceBook = Workbooks.Open(SourceFile)
SourceBook.Sheets(1).Copy After:=ThisWorkbook.Sheets(1)
SourceBook.Close SaveChanges:=False
ActiveSheet.Name = "2 line"


Why is there a difference when running this from a shortcut/hot key???
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Excel can be a bit funny about certain keys though usually it is the Shift key that is an issue for things like this, since it is used to suppress event code running when opening a workbook. I suspect it may be because you are opening a csv file (they have their own quirks, such as forcing a recalculation when opened) but I confess it's not a bug I've heard of before. Which version of Office are you using?
 
Upvote 0
Excel can be a bit funny about certain keys though usually it is the Shift key that is an issue for things like this, since it is used to suppress event code running when opening a workbook. I suspect it may be because you are opening a csv file (they have their own quirks, such as forcing a recalculation when opened) but I confess it's not a bug I've heard of before. Which version of Office are you using?
Thanks RoryA. I am using Office 2019, VBA 7.1.

I tried saving the .csv file as an .xlsx file and had the same results. Works fine unless using a hot key/shortcut. Then it opens the file and stops.
 
Upvote 0
And you’re definitely not using the Shift key in the shortcut? What OS and what are your regional settings? US?
 
Upvote 0
Hi Rory. As I said in the original post, the shortcut is CTRL+J or CTRL+SHIFT+J. I have also used CTRL+SHIFT+M to isolate the subroutine. OS is Windows 10, and region is US. Sorry that I didn't mention that before.
 
Upvote 0
Shift is a definite problem, so make sure you aren’t including that in the key combo
 
Upvote 0
Solution
Shift is a definite problem, so make sure you aren’t including that in the key combo
Thanks Rory. Why is that? I'll see if I can find an unusued control key without the shift and see if that makes a difference.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top