+ Reply to Thread
Results 1 to 10 of 10

Malfunctioning Cut/Paste Macro with Formula in criteria column

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    Midwest
    MS-Off Ver
    Excel 2007, Access 2007
    Posts
    18

    Malfunctioning Cut/Paste Macro with Formula in criteria column

    Hey Everyone,
    So I have been trying to hone my coding skills, and discovered a pretty decent code to move rows of information from one sheet to another based on the word "Done" in the criteria column (using Autofilter)....BUT...I am running into some snafu's that I can't quite iron out.

    The code i am using I borrowed from a previous thread, http://www.excelforum.com/excel-prog...ain-value.html, and here are the two problems:
    1. I would like the last column (N) on the Progression sheet to automatically populate "Done" when a date is entered into the cell in column M. I used the formula =IF(ISBLANK(M2), "", "Done") which works only for the very first time I run the macro...the formula then stops functioning for some reason once new data is entered into the Progression sheet(event though the formula still exists in the cell). I know I could just drag and copy "Done" for the cells I want it to apply to, but I am trying to automate the experience for the users.
    2. The second minor issue is that the Autofilter on the Progression sheet will not shut off at the end of the macro. I thought I had the appropriate code line, but that is not the case.
    Please see the code below and the attached spreadsheet. Thanks everyone!

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Malfunctioning Cut/Paste Macro with Formula in criteria column

    Try this...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-30-2012
    Location
    Midwest
    MS-Off Ver
    Excel 2007, Access 2007
    Posts
    18

    Re: Malfunctioning Cut/Paste Macro with Formula in criteria column

    Hey AlphaFrog, thanks for the quick response. The changes to the code did not fix the autofilter problem. It appears that my formula in the criteria column in "Progression" is maintained and functioning now, BUT, the page just stays on "Complete" (whereas before it would stay on "Progression") AND the autofilter on the Progression sheet is still applied. It isn't imperative that it goes back to the Progression page after macro completion, but I really would like it to remove the autofilter automatically, or else my users may be confused as to where the data went. Thanks again!

  4. #4
    Registered User
    Join Date
    10-30-2012
    Location
    Midwest
    MS-Off Ver
    Excel 2007, Access 2007
    Posts
    18

    Re: Malfunctioning Cut/Paste Macro with Formula in criteria column

    AlphaFrog (or any other VBA geniuses), one quick question: Do you know how I would use the same coding to move rows based on a date range? So rather than basing the autofilter on "Done" in Column N, provide an input box that would have a start date and end date to create the autofilter?

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Malfunctioning Cut/Paste Macro with Formula in criteria column

    The Autofilter mode turned off with the example workbook you provided. It didn't switch back to Progression, but that could easily be added.

    Sheets("Progression").Select

    Are you using the exact same code as above or did you maybe edit a different version of the macro?

  6. #6
    Registered User
    Join Date
    10-30-2012
    Location
    Midwest
    MS-Off Ver
    Excel 2007, Access 2007
    Posts
    18

    Re: Malfunctioning Cut/Paste Macro with Formula in criteria column

    I copied your modified code exactly, and pasted it into my VBE for the workbook. Let me clarify that I would like autofilter turned off for the Progression sheet, because the autofilter is turned off for the Complete sheet.

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Malfunctioning Cut/Paste Macro with Formula in criteria column

    Try this at the end of the sub

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    10-30-2012
    Location
    Midwest
    MS-Off Ver
    Excel 2007, Access 2007
    Posts
    18

    Re: Malfunctioning Cut/Paste Macro with Formula in criteria column

    Sorry AlphaFrog , now it goes back to the Progression sheet, but Autofilter is still toggled on, so any records without "Done" appear non-existent.

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Malfunctioning Cut/Paste Macro with Formula in criteria column

    Quote Originally Posted by dlab85 View Post
    Sorry AlphaFrog , now it goes back to the Progression sheet, but Autofilter is still toggled on, so any records without "Done" appear non-existent.
    I don't know what to tell you. It should work.

    One last try...

    Replace this...
    Sheets("Progression").AutoFilterMode = False

    With this...
    My_Range.AutoFilter

  10. #10
    Registered User
    Join Date
    10-30-2012
    Location
    Midwest
    MS-Off Ver
    Excel 2007, Access 2007
    Posts
    18

    Re: Malfunctioning Cut/Paste Macro with Formula in criteria column

    No worries AlphaFrog, I can always manually toggle the autofilter off. Thanks for the help anyways.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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