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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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?
 

bkahn

New Member
Joined
Oct 19, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
And you’re definitely not using the Shift key in the shortcut? What OS and what are your regional settings? US?
 

bkahn

New Member
Joined
Oct 19, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Shift is a definite problem, so make sure you aren’t including that in the key combo
 
Solution

bkahn

New Member
Joined
Oct 19, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It’s a bug.
 

Forum statistics

Threads
1,140,926
Messages
5,703,215
Members
421,282
Latest member
hogie

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
Top