+ Reply to Thread
Results 1 to 9 of 9

move entire row to another worksheet based on cell value

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    8

    Red face move entire row to another worksheet based on cell value

    Hi,

    I have a similar scenario as to the link below:

    http://www.excelforum.com/excel-prog...ell-value.html

    I see that this Spreadsheet has been solved, I had a look at that spreadsheet and it works perfectly..
    I tried to use that codes and just change the sheet names and destinations, but with no luck.

    I have a large Workbook, with a main sheet (with all outstanding orders), and 11 sheets for distributors. On the main sheet Column "T", Order Status has Data Validation, where I'd like to choose eg. "1st Distribution" and that whole row must be moved (and deleted) to the "1st Distribution" sheet and so forth for the other Distributor's sheets..
    All the sheets columns and rows are exactly the same to make the coding easier to work - after I've done this, I'll hide rows that I don't need in that sheet..



    I'll appreciate some help on this

    Please let me know if someone will be able to help me

    Much Appreciated
    Berndene
    Last edited by Berndene; 09-14-2012 at 10:22 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: move entire row to another worksheet based on cell value

    Hello there,

    The link you have supplied for the previous thread does not work. Can you please just post the code that you are working with and a sample of you workbook and we'll try and help you from there?

    Thanks!

  3. #3
    Registered User
    Join Date
    09-12-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: move entire row to another worksheet based on cell value

    Oh I'm glad to see a reply
    Hello There,

    Here's the post that I worked off, and i used that code and made some amendment, but deleted it when it didn't want to work for my situation..


    http://www.excelforum.com/excel-programming-vba-macros/790860-move-entire-row-to-another-worksheet-based-on-cell-value.htmlhttp://
    Quote Originally Posted by stanleydgromjr View Post
    bdf0827,

    Thanks for the workbook.


    Detach/open workbook Worksheet_Change Event wC colD wB wD bdf0827 - EF790860 - SDG12.xlsm and make changes to the DataValidation cells in worksheet Current in range D7:D26.

    Then check the other two worksheets.



    If you want to use the macro on another workbook:


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Select the worksheet in which your code is to run, worksheet Current
    3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
    4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    5. Press the keys ALT + Q to exit the Editor, and return to Excel


    [Select Code] copy to clipboard


    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' stanleydgromjr, 09/03/2011
    ' http://www.excelforum.com/excel-prog...ell-value.html
    If Intersect(Target, Range("D7:D26")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then Exit Sub
    Dim NR As Long
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    Select Case Target.Value
    Case "Booked"
    NR = Worksheets("Booked").Range("D29").End(xlUp).Offset(1).Row
    Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("Booked").Range("A" & NR)
    Case "DNMQ"
    NR = Worksheets("DNMQ").Range("D29").End(xlUp).Offset(1).Row
    Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("DNMQ").Range("A" & NR)
    End Select
    .EnableEvents = True
    .ScreenUpdating = True
    End With
    End Sub


    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then make changes to the DataValidation cells in worksheet Current in range D7:D26.

    Then check the other two worksheets.
    Quote Originally Posted by gjlindn View Post
    Rolls data up to fill empty row
    [Select Code] copy to clipboard

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 And Target.Cells.Count = 1 Then MoveMe Target
    End Sub

    Sub MoveMe(rTarget As Range)
    Dim wsMoveTo As Worksheet
    Dim rMoveToRow As Range
    Dim rCopyFrom As Range
    Dim rCopyTo As Range
    Dim rItem As Range
    Dim rMoveUp As Range
    Dim bEvents As Boolean
    Dim bScrUpd As Boolean
    Dim lCalc As Long

    With Application
    bEvents = .EnableEvents
    bScrUpd = False
    lCalc = .Calculation
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    End With


    Select Case rTarget.Text
    Case "Booked", "DNMQ"
    Set wsMoveTo = Sheets(rTarget.Text)
    Case Else
    Exit Sub
    End Select
    Set rMoveToRow = wsMoveTo.Cells.Find("Potential", LookIn:=xlValues, LookAt:=xlWhole).End(xlUp).Offset(1)
    Set rCopyFrom = Range(Cells(rTarget.Row, "A"), Cells(rTarget.Row, "J"))
    Set rCopyTo = wsMoveTo.Range(wsMoveTo.Cells(rMoveToRow.Row, "A"), wsMoveTo.Cells(rMoveToRow.Row, "J"))
    rCopyTo.Formula = rCopyFrom.Formula
    Set rMoveUp = Range(Cells.Find("Potential", LookIn:=xlValues, _
    LookAt:=xlWhole).End(xlUp).Offset(1), rCopyFrom)
    rMoveUp.Formula = rMoveUp.Offset(1).Formula


    Set wsMoveTo = Nothing
    Set rMoveToRow = Nothing
    Set rCopyFrom = Nothing
    Set rCopyTo = Nothing

    With Application
    .EnableEvents = bEvents
    .ScreenUpdating = bScrUpd
    .Calculation = lCalc
    End With
    End Sub
    Attached Files Attached Files
    Last edited by Berndene; 09-14-2012 at 03:12 AM.

  4. #4
    Registered User
    Join Date
    09-12-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: move entire row to another worksheet based on cell value

    The original Workbook has a Main Sheet and 11 Disti sheets, had to remove some of the Disti Sheets as the file was too big...

  5. #5
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: move entire row to another worksheet based on cell value

    Can you please just describe what you are trying to do?

    Thanks!

  6. #6
    Registered User
    Join Date
    09-12-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: move entire row to another worksheet based on cell value

    Hi,

    I have a Main Sheet with outstanding Orders, Once a order has been placed, I'd like to select the Distributor in Column T (Order Status), and that whole row must move to that Distributors sheet, eg First Distribution - and it must be deleted off the Main Sheet

    Does that make sense?
    Last edited by Berndene; 09-14-2012 at 10:15 AM.

  7. #7
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: move entire row to another worksheet based on cell value

    Hello there,
    Try the following code, because the code deletes row, please make a backupcopy of your workbook and then test this code so that if it is not what you are looking for you do not loose information.

    Steps to insert the code into your workbook:

    1. Select the MAIN worksheet.
    2. Right click on the worksheet's tab and select the View Code option
    3. Copy and paste the below code in the blank section provided, anything that appears in green is comment meant to help you understand the code:

    Please Login or Register  to view this content.
    4. Close out of Visual Basic.
    5. Change the status of an order in column T

    let me know if this works for you.

    Thanks!

    RVASQUEZ

  8. #8
    Registered User
    Join Date
    09-12-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: move entire row to another worksheet based on cell value

    Gheeeeee Whizzzzz!!!!!!

    Works better than I thought!!!! Even when I try the workbook with more Disti Sheets - A-freakin-mazing!!!

    Thank you sooooo much for your help!!

    Definitely worth the wait

    Berndene (very happy)

  9. #9
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: move entire row to another worksheet based on cell value

    No problem! Glad to help, please remember to mark this thread solved and maybe give a little star tap to add to my reputation points if I helped!

    Thanks!

+ 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