+ Reply to Thread
Results 1 to 15 of 15

Move rows from sheet1 to sheet2 based on a field value

  1. #1
    Registered User
    Join Date
    02-10-2016
    Location
    Bratislava, Slovakia
    MS-Off Ver
    MS 2010
    Posts
    11

    Move rows from sheet1 to sheet2 based on a field value

    Hello
    could you please help me to write a macro to move rows from sheet Planning 2016 to the sheet Complete based on status column I; sheet Planning 2016.
    The one I used does not work..see enclosed excel file.
    Many thanks in advance
    Ivana
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Move rows from sheet1 to sheet2 based on a field value

    Does this help?

    Please Login or Register  to view this content.

  3. #3
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Move rows from sheet1 to sheet2 based on a field value

    Try this macro-

    Please Login or Register  to view this content.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  4. #4
    Registered User
    Join Date
    02-10-2016
    Location
    Bratislava, Slovakia
    MS-Off Ver
    MS 2010
    Posts
    11

    Re: Move rows from sheet1 to sheet2 based on a field value

    no I get this error message see enclosed screen shot
    Attached Images Attached Images

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Move rows from sheet1 to sheet2 based on a field value

    Can't read your screen shot. What is the message and what is the associated number?

  6. #6
    Registered User
    Join Date
    02-10-2016
    Location
    Bratislava, Slovakia
    MS-Off Ver
    MS 2010
    Posts
    11

    Re: Move rows from sheet1 to sheet2 based on a field value

    sorry, I found a small spelling mistake, it works perfect ! thanks!!
    now I still would like to add a "move" button to execute the macro, do you know how?

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Move rows from sheet1 to sheet2 based on a field value


  8. #8
    Registered User
    Join Date
    02-10-2016
    Location
    Bratislava, Slovakia
    MS-Off Ver
    MS 2010
    Posts
    11

    Re: Move rows from sheet1 to sheet2 based on a field value

    Hello John, how can I change the macro that it starts copying on the 5th row (not the 1st one) on the sheet complete?

  9. #9
    Registered User
    Join Date
    02-10-2016
    Location
    Bratislava, Slovakia
    MS-Off Ver
    MS 2010
    Posts
    11

    Re: Move rows from sheet1 to sheet2 based on a field value

    Hello John, this macro after moving all completed rows and running it again it removes every row one after the other (not taking care about the status). Do you know why this could be?

    Sub CommandButton21_Click()

    Range("I4:I" & Range("I" & Rows.Count).End(3).Row).AutoFilter 1, "*Complete*"
    With Range("I5:I" & Range("I" & Rows.Count).End(3).Row).SpecialCells(12).EntireRow
    .Copy Sheets("Complete").Range("A" & Rows.Count).End(3)(2)
    .Delete
    End With
    ActiveSheet.AutoFilterMode = False


    End Sub

  10. #10
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Move rows from sheet1 to sheet2 based on a field value

    Not sure. I can't duplicate that. It works for me when I tested it. Can post a workbook where it does that?

  11. #11
    Registered User
    Join Date
    02-10-2016
    Location
    Bratislava, Slovakia
    MS-Off Ver
    MS 2010
    Posts
    11

    Re: Move rows from sheet1 to sheet2 based on a field value

    sure, here it is
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    02-10-2016
    Location
    Bratislava, Slovakia
    MS-Off Ver
    MS 2010
    Posts
    11

    Re: Move rows from sheet1 to sheet2 based on a field value

    Hello John
    Did you have chance to test it in the file I have sent you?
    Kind Regards
    Ivana

  13. #13
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Move rows from sheet1 to sheet2 based on a field value

    Quote Originally Posted by ivanka2000 View Post
    Hello John
    Did you have chance to test it in the file I have sent you?
    Kind Regards
    Ivana
    Yes. It still works for me?

  14. #14
    Registered User
    Join Date
    02-10-2016
    Location
    Bratislava, Slovakia
    MS-Off Ver
    MS 2010
    Posts
    11

    Re: Move rows from sheet1 to sheet2 based on a field value

    it moves all rows to the sheet Complete regardless the status "completed". So if I do not set any row to complete, and push the "move" button, it moves the rows line by line anyway.

  15. #15
    Registered User
    Join Date
    02-10-2016
    Location
    Bratislava, Slovakia
    MS-Off Ver
    MS 2010
    Posts
    11

    Re: Move rows from sheet1 to sheet2 based on a field value

    Hello, is it possible to add to copy also formatting e.g. colour of the filled cells from the entire row (incl.schedules in calendar)?

+ 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] Macro for copying rows in sheet1 to end of existing rows in sheet2 based on criteria
    By Karl Gustaf Karsten in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2015, 04:19 AM
  2. move data from sheet1 to sheet2 based on date in column1 of sheet1
    By pcaldwell in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-10-2014, 03:20 PM
  3. Replies: 2
    Last Post: 10-16-2012, 11:34 AM
  4. [SOLVED] Move data from SHEET1 to SHEET2 based on Selected criteria...like an adv filter
    By gspivey79 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-03-2012, 02:06 PM
  5. Replies: 1
    Last Post: 03-22-2012, 04:08 AM
  6. need to sum up in sheet2 and move all the date from sheet2 to sheet1
    By vijay1153 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2012, 01:16 PM
  7. Help! Hiding rows in sheet2 based on values in sheet1
    By Oti in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-23-2011, 12:47 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