+ Reply to Thread
Results 1 to 13 of 13

When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

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

    When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Macro below should do following:

    When workbook opens, checks Col C of the "Approved" sheet.

    If any rows are dated earlier than today, it moves the row to the first free row of tne "Expired" sheet, and deletes the blank row in the "Approved" sheet.

    Please Login or Register  to view this content.
    All suggestions and guidance welcomed as ever

    Ochimus
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Looks mostly good. I changed the multicell selection to a range and explicitly used the sheet so that after the Copy it knows you're still on Sheet2 the next time around.
    When you delete a cell, you skip it when running a For i = Start to End, so I use a For i = End to Start Step -1 so that no rows are skipped.
    Lastly I adjusted the delete object to Rows instead.

    Please Login or Register  to view this content.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

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

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    daffodil11,

    Many thanks for prompt response, but it hasn't worked.

    File opens, and nothing gets cut and shifted!

    Can't see anything "odd" in my Excel, as everything seems to be working normally when I "do various things"

    If it works on your machine, can you attach the file with a few "dead" dates that should shift across when I open it this end?

    Ochimus

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    How about this one?
    Please Login or Register  to view this content.
    Last edited by bakerman2; 03-29-2016 at 06:44 PM.

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

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Not good news, sadly,

    I tried both approaches, but neither work.

    I've attached two versions of the file using the different macros, with two "expired" dates in the Approved sheet that should disappear onto the Expired sheet when the file opens.

    Look forward to hearing from you, for good or ill. . .

    Ochimus
    Attached Files Attached Files

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

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Sorry, not sure why the Bakerman version dropped off.

    Ochimus
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Delete this line

    Please Login or Register  to view this content.
    Kind regards
    Leo

  9. #9
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Or use it like this.
    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    and all 3 will work

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Don't remove Option Explicit, add a declaration for the variable c.
    Please Login or Register  to view this content.

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

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Enormous thanks to everyone for the inputs.

    I now have a working solution using Norie's option, as I had it beaten into me from the start that you should always use Option Explicit to save a lot of grief down the line.

    Ochimus

  13. #13
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: When file opens, Macro not moving expired record(s) to other sheet and deleting row(s)

    Although according to all the Experts and Guru's it's nearly sacrilidge of not declaring variables it's infact not really necessary in 99% of the cases. Codes work as well with or without declarations.
    It's just a matter of keeping your head with it when coding. I'm writing code now for almost 20 years and up until now no one ever said to me code didn't work because of not declaring.
    But I almost got burned on the stakes once for starting the discussion here about declaring variables so I'm not gonna do it again. This is just my personal opinion.
    If you feel good about it do it else don't.

    But not in the least thanks for the rep
    Last edited by bakerman2; 03-30-2016 at 07:52 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] Userform code for deleting record on sheet
    By Nitefox in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2014, 12:47 AM
  2. [help] expired macro excel file
    By jamzsaludares in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-25-2014, 02:15 AM
  3. Replies: 0
    Last Post: 07-23-2013, 04:02 PM
  4. [SOLVED] Moving a row to another sheet and deleting the empty row
    By Tigger118 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-25-2013, 09:31 AM
  5. Improvement - Changing Infraction record once expired / reversed
    By Russell Dawson in forum Suggestions for Improvement
    Replies: 46
    Last Post: 09-15-2012, 01:49 PM
  6. Excel 2003 Deleting a row and moving it to another sheet
    By DCarmen352 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-10-2012, 01:32 AM
  7. [SOLVED] Move record to different sheet and then deleting it through a userform command button
    By dev111ski in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-31-2012, 06:30 AM
  8. Moving The Selected Record From One Sheet To Another
    By LoveCandle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-01-2006, 12:50 AM

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