Code runs using F8 or F5 but not using the keyboard shorcuts?

jamieboss

New Member
Joined
Feb 22, 2010
Messages
25
Hi there,

I've written a macro which is meant to copy the contents of the current row then open another workbook from a standard location, locate the same line and paste in the copied information. The macro is then meant to save and close the workbook it opened with the changes. The 'VesselID' variable is used to store the value of the unique identifying value which is used to find the correct row in the workbook we paste the info into.

My problem is that the code works perfectly when I run it manually from inside VBA (using either F5 to run the whole script or F8 to run it line by line) but only runs first half of the code when I use the keyboard shortcut i recorded?

Any thoughts or assistance are greatly appreciate! I have pasted the actual code below:

Sub MasterlistItemUpdate()
'
' MasterlistItemUpdate Macro
' Macro recorded 7/8/2011 by Jamie Edwards
'
' Keyboard Shortcut: Ctrl+Shift+M

Dim VesselID As String

'Moves the cursor to the cell on the far left which contains the vessel ID
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select

'Copies the row contents ready to paste into the masterlist file
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Select

'Assigns the variable name to the specfic vessel ID
VesselID = Selection.Value

'Opens the masterlist file and searches for the correct line
Workbooks.Open Filename:= _
"C:\Bar-i\Clients\DropBox\Bar-i Masterlist.xls"
Range("A:A").Select
Selection.Find(What:=VesselID, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select

'Pastes in the updated information then saves and closes the file
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWindow.Close

End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Brandon,

Thanks for your interest but I just figured it out. It's due to a bug in excel which is related to a conflict between using the 'open workbook' method and having the 'Shift' key pressed. In practical terms this means if your macro opens a workbook, you cannot have the Shift key as part of the keyboard shortcut to fire the macro. I simply changed the shortcut from 'Ctrl' + 'Shift' and 'J' to 'Ctrl' + 'J'.

Thanks again,

Jamie
 
Upvote 0
Ahhh... had something similar when I was sending a keypress. Certain situation it would kick out and took forever to figure it was the keypress. Glad you figured it out. Always the best feeling when you do.
 
Upvote 0

Forum statistics

Threads
1,224,578
Messages
6,179,654
Members
452,934
Latest member
mm1t1

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