Results 1 to 7 of 7

Thread: moving rows between sheets

  1. #1

    moving rows between sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    date call info lead


    Hello,
    I'm a real newb and any help would greatly appreciated.
    I have 3 sheets that contains contact info. named "hot" "cold" and "lost".
    I would like it so that when "cold" is entered in the "lead" column, the row moves to the "cold" sheet and if "lost" is entered moves to "lost" sheet, if nothing is entered the row stays on sheet. The same thing for the other 2 sheets.
    There are 12 columns, lead is col. "L" and row "1" is header.
    I tried this code on hot sheet, but when it runs I get: run time error "9", subscript out of range.
    Any help would greatly appreciated, thanks
    Code:

    Private Sub Worksheet_Change(ByVal Target As Range) Dim lr As Long With Target If .Rows.Count = 1 And .Column = Columns("L").Column Then If LCase(.Value) = "cold" Or LCase(.Value) = "lost" Then .EntireRow.Copy lr = Worksheets(.Value).Cells(Rows.Count, "L").End(xlUp).Row Worksheets(.Value).Paste Worksheets(.Value).Cells(lr + 1, "A") Application.EnableEvents = False .EntireRow.Delete Application.EnableEvents = True End If End If End With End Sub
    Last edited by dpotter13; 2014-10-25 at 09:20 PM.

  2. #2
    Hi,
    Check if this can help you out:
    httppp://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/

    (delete the 2 "p"s after http as I can't post links yet)
    Cheers!

  3. #3
    Thanks,

    I have 2 questions;
    1- do the cells have to be the same size on all the sheets-some have been resized depending on info entered?
    2- will the paste func put it the first empty row on the other sheets?

    I have attached the wb-after deleting personal info
    Attached Files Attached Files
    Last edited by dpotter13; 2014-10-26 at 02:22 PM.

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    @ dpotter13,

    Your code is referring to your sheets as "cold" and "lost" instead of "Cold Leads" and "Lost Leads".
    Remove the space and Leads from the actual sheet names and your code executes as expected.

  5. #5
    Thank you so much..worked perfectly

  6. #6
    Update..worked in test WB but not in real WB. I renamed sheets and copied code...error 9 comes up...thoughts

  7. #7
    Nevermind..forgot to rename in ALL lower case

Posting Permissions

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