Worksheet_change event with select case and copy paste or autofill

  • Hi, I have reached the limit of my ability on this one. My code checks for an order number and if it exists updates a tracking sheet with todays date. If the order number does not exist it creates a record on the tracking sheet and records todays date in the relevant status code. Works great for single instances however the user needs to be able to copy and paste a batch of statuses at once and the code is not running in that instance.


    I am sure the select case can be written better however that is not the primary concern at the moment. Also, the error handling is there just to prevent error messages until I can sort out the functionality.


    Can you please help me fire the Select Case when the change event is a copy & paste or even an autofill drag.


    Thanks in advance.


  • You should not use error handlers until you know what is happening with the code. Error handlers will suppress errors which might help solve the issue.


    Which sheet is StatusRng on? Is it a different sheet to the one with code in?


    Nothing is selected in the code so what is VLOOKUP looking for?


    Is the order number a constant or generated by a formula?


    Application.VLookup would need to be Application.WorkSheetFunction.VLookup, but I would use COUNTIF instead.


    I've tried to understand your code but without seeing the workbook it's difficult to understand what sheets you are using. Attach an example of the workbook

  • Thanks for taking the time to look Roy,


    There are 2 sheets pertinent to the code. HTL and Tracking.


    StatusRng is on the HTL sheet. The Tracking sheet is hidden and will not be used in everyday work, it is trying to capture dates of events to build up a timeframe of work passing though the facility.


    Vlookup is taking the selected cell in StatusRng and looking across to pick up the order number. In the attached, simplified workbook, that is col C.

    The order number is a constant allocated by the sales team. On occasion there will be no order number and these should be ignored which is why I put in the If IsEmpty statement.


    I have attached a cut down workbook. When a status is applied in the HTL sheet the code does the following:


    1. Check if order number exists - if no then exit
    2. If order number exists then check Tracking sheet for that order number
    3. If order number does not exist on Tracking sheet, write to new row and enter date in relevant status column
    4. If order number does exist on Tracking sheet, find row and enter date in relevant status column.

    Works fine on single changes however not on copy / paste of several rows. So if the user wants to change 10 rows to a status of Production he is changing the first one then copying that status and pasting to the remaining rows. That throws a type mismatch error on Ln73 of the code which is between the two Case statements.

    CurrRow = Application.WorksheetFunction.Match(result, ws.Range("B:B"), 0)


    In order to get things working again I need to stop the debugger and in the immediate window run Application.EnableEvents = True


    Cheers,

    Files

    • CodeSample.xlsm

      (27.86 kB, downloaded 39 times, last: )