+ Reply to Thread
Results 1 to 5 of 5

Macro not identifying correct worksheet to move record.

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Macro not identifying correct worksheet to move record.

    DATABASE sheet on attached lists various jobs.
    STATUS sheet should list jobs that are currently scheduled and those that have been completed.

    Sheet Change macro below has two issues:
    (a) Macro does not "fire" every time a number is added or changed in Col K of the Database, even though I have set Application.EnableEvents = True at the outset AND declared it in the Immediate window.

    (b) When it does work it copies the relevant record perfectly to the CURRENT Section of the STATUS sheet, but if a date is then entered in Col M it is not moving the record in the STATUS from Scheduled to Completed, and when it copies the Completed Date from the Database it is posting it in Col 6 and not Col 5.

    Please Login or Register  to view this content.
    All solutions, suggestions and alternatives welcome as ever.

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 12-31-2020 at 09:46 PM.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro not identifying correct worksheet to move record.

    Hi Ochimus...Why not make use of listObjects which increase or decrease depending on entries...see attached as an example...

    was not sure which unique qualifier to make use of when Moving from Current to Complete...so made use of Description...

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 01-01-2021 at 09:05 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Macro not identifying correct worksheet to move record.

    sintek,

    Thanks for the prompt suggestion, and apologies if my original description of the requirement seemed unclear to you?

    Stage 1: Copy five elements of a record in DATABASE to the "Current" section in the STATUS sheet:
    Macro triggered if User adds or changes a number in Col K of the Database sheet.
    Col K is an "audit trail" showing the viewer whether the proposed completion date in Col H has been changed and, if it has, how many times.
    That element in my Macro runs perfectly (if the Macro actually fires!)

    Stage 2: Move STATUS record from Current to Completed - This is the element that didn't work.
    The "trigger" that fires the Macro is entering a date in Col M of the DATABASE sheet.
    The "qualifier" is the Contract Number, which the Macro uses to find the matching record in the Current section of STATUS (rows 3 - 10 in mine, cols A - C in your rework), move three elements to the "Completed" bloc in rows 14 - 17 in mine, cols G - I in your rework, and copy the Completion date in Database Col M to Col E of the STATUS row (Col J in your rework)
    For whatever reason the Code doesn't seem to recognise the relevant record, or move it to the Completed section?

    Hope this clarifies.

    Ochimus
    Last edited by Ochimus; 01-01-2021 at 11:28 AM.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro not identifying correct worksheet to move record.

    Enter number in Col K. Record copies to rows 3 - 10 in sheet 2
    Now enter Completed date in Col M. Record in sheet 2 should moves to rows 14 - 17, and Completed Date copies to Col E on sheet 2
    Supplied code and sample file does just that...
    Should find the matching record in the Current section
    The "qualifier" is the Contract Number
    This is however not unique, so how do you distinguish if you have both Desc1 & Desc4 moved over to Current...
    Both have same Contract...Not unique...

    Just for sample file I made use of Description as qualifier as this was the only unique part...

    All that needs to change is the coding that references this unique qualifier...Red snippets below change to reference Contract no
    Please Login or Register  to view this content.
    Unless I misunderstood entirely...
    Last edited by sintek; 01-01-2021 at 11:45 AM.

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro not identifying correct worksheet to move record.

    Seems you not interested in my solution...Here is THE FIX for your code...
    Does exactly the same as my suggested improved solution...
    Just have not incorporated Clear or Delete from Current table...

    By the way if you delete the row, you going to run into problems as your code is hard coded to look for specific rows...
    i.e.
    Please Login or Register  to view this content.
    This is going to create empty rows in Completed table...
    Also...if you have multiple entries in Current table, your code is not going to work as Contract no's are not unique...Code will always find the first of searched Contract no...

    Anyway, my two cents...
    Please Login or Register  to view this content.
    Last edited by sintek; 01-02-2021 at 11:53 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Row hidden while running macro to move record from one sheet to other
    By sarat47 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2018, 03:09 AM
  2. Move column data from a master to correct worksheet in same workbook
    By sagemm1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-20-2013, 07:08 PM
  3. [SOLVED] Need Macro to move multiple rows into a single row for each 'Record ID'
    By jonathanseah.87 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-05-2012, 10:21 AM
  4. Move record from worksheet B to A based on a condition
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-27-2011, 12:48 AM
  5. Identifying last record (de-duping)
    By md1972 in forum Excel General
    Replies: 7
    Last Post: 12-07-2009, 10:12 AM
  6. What function would move a closed record to another worksheet?
    By Stephen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-27-2006, 01:30 AM
  7. Replies: 2
    Last Post: 03-13-2006, 01:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1