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
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