VBA Keyboard shortcut stop in middle of running

[ichbinich]

I have created a macro like this

Set x = ActiveWorkbook
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Workbooks.Open Filename:="C:\Users\{user}\Desktop\GP MACRO\Bulk inventory adjustment.xlsx"
Windows("Bulk inventory adjustment.xlsx").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete
With x.Activate
Selection.Copy
End With
Windows("Bulk inventory adjustment.xlsx").Activate
Range("A1").Select
ActiveSheet.Paste
ActiveWorkbook.Save
Windows("Bulk inventory adjustment.xlsx").Close
With x.Activate
Dim xFullName As String
xFullName = Application.ActiveWorkbook.FullName
ActiveWorkbook.Saved = True
Application.ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill xFullName
Application.ActiveWorkbook.Close False
End With

it works fine when i run it manually from developer/ macro
but when i press the keyboard shortcut it stop before line 18 with no error given

 

[GWteB]

Not sure what you're trying to accomplish, but your code does not compile so I wonder how you did manage to get this code to run.
Tell us what you want and perhaps there's someone able to help you.

 

[ichbinich]

Im receiving a csv file in email everting what i need to update in the inventory
as of this file is always a different name and to import it in our system (dynamics GP) it need to be in some file with different name like Bulk inventory adjustment
So im running a macro to open the inventory adjustment sheet paste all data there and save n close the file and delete the CSV file

so when i'm running the macro it works fine
but i added a shortcut it works only until after saving the inventory adjustment sheet
but the sheet stays open and it not deleting the other file


could be there is better code for that but i'm not a pro in VBA at all so when i need a macro I'm recording it and make the necessary changes it should work

 

[GWteB]

your code does not compile so I wonder how you did manage to get this code to run.

Consider this unwritten, there was a "bug" on my end that prevented your code from compiling.

So I was able to reproduce the behavior you described. Not only with your code, but also with mine.
Your code stopped after line 5, mine after line 4. In both macros immediately after opening the intended target workbook. This only occurred when a shortcut was assigned that used the Shift key (Ctrl+Shift+key) and the macro was invoked using that shortcut. So then it dawned on me and I should have known.

After all, opening a workbook (from within Excel) with the Shift key pressed disables all macros, regardless whether it's done manually or through VBA***. In previous versions of Excel, when there was no Trust Center, this way any workbook could be opened safely without executing any Workbook_Open() or Auto_Open() event handler, and it still can.
So bottom line: don't use the Shift key in your shortcut when the invoked process is supposed to open a workbook (or any other file using the Application.Workbooks.Open method).

Your code could be slightly more compact and a little more readable. Such a version is below.
Note that in most situations there's no need for activating and/or selecting workbooks, worksheets and cells.
Once a proper reference is set we can use that reference elsewhere in our code.

Sub ichbinich()

    Dim ShtSource As Worksheet
    Set ShtSource = ActiveWorkbook.ActiveSheet

    Dim ShtDestination As Worksheet
    Set ShtDestination = Application.Workbooks.Open(Filename:="C:\Users\{user}\Desktop\GP MACRO\Bulk inventory adjustment.xlsx").ActiveSheet
    
    With ShtDestination
        .Cells.Delete
        ShtSource.UsedRange.Copy Destination:=.Range("A1")
        .Parent.Save
        .Parent.Close
    End With
    
    ' delete source workbook unconditionally(!)
    With ShtSource.Parent
        Dim FileFullName As String
        FileFullName = .FullName
        .Saved = True
        .Close False
    End With
    Kill FileFullName
End Sub

*** When it's done through VBA the Shift key's state at the start of any process is decisive and not the moment when a workbook is actually opened. So it's no use in implementing a small delay or even displaying a message box.

 

[ichbinich]

yep the shift key was the problem
the reason why I'm using shift key is because i don't want to overwrite Microsoft shortcuts
but i found ctrl m doesn't look like be used in excel
Please correct me if I'm wrong

And thanks for again for your time and help and for revised code

 

[GWteB]

You're welcome and thanks for the feedback

With Ctrl+m you're good. A list of Excel shortcuts can be found over here ...

ASAP Utilities for Excel - Excel shortcuts - A list of Excel's shortcut keys - Excel tips

A list of Excel's shortcut keys - Excel tips. This list gives you a glance of (nearly) all shortcut combinations in Excel. Please take the time to read it. I'm sure you'll find many of them useful.

www.asap-utilities.com

 

[ichbinich]

They skipped Ctrl+e
it is a very powerful tool

How To Use Ctrl + E In Microsoft Excel? - My Microsoft Office Tips

www.myofficetricks.com

 

[GWteB]

The FlashFill shortcut is indeed missing in their list ...
Didn't notice that, thanks!