Results 1 to 5 of 5

Thread: Move a row to another sheet based on cell value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Move a row to another sheet based on cell value

    Hello,

    I've been using this site for awhile now and it's been very helpful. Thanks. In most cases, I could find what I need or at least find something close enough that I could modify it to work. I've tried the same approach this round, with no luck. My excel document has 2 sheets that I'm working with "Report" and "Backlog (Amazon)". What I'm trying to do is use a button to move rows from "Report" to ""Backlog (Amazon)" and clear content from "Report" based on column B containing the words "Amazon" or "Golden State". What I'm struggling with is the "containing" aspect. Each row that I want to move will have Amazon or Golden State in it, but with multiple variations (Amazon.com INC, Amazon.com Services, INC, etc.). Any help is much appreciated.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,059
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Move a row to another sheet based on cell value

    see if this will work for you.

    Code:
    Sub t()
    Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range
    Set sh1 = Sheets("Report")
    Set sh2 = Sheets("Backlog (Amazon)")
    Set rng = sh1.Range("B1", sh1.Cells(Rows.Count, 2).End(xlUp))
    rng.AutoFilter 1, "*Amazon*", xlOr, "*Golden State*"
    rng.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
    rng.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.ClearContents
    sh1.AutoFilterMode = False
    End Sub
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move a row to another sheet based on cell value

    That worked perfect. Thanks.

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,059
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Move a row to another sheet based on cell value

    Quote Originally Posted by NRIANS2123 View Post
    That worked perfect. Thanks.
    You're welcome,
    regards, JLG
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move a row to another sheet based on cell value

    One last question. Is there a way to tweek the line in red to only past columns A thru BD instead of the entire row?

    Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range
    Set sh1 = Sheets("Report")
    Set sh2 = Sheets("Backlog Report (Amazon)")
    Set rng = sh1.Range("B1", sh1.Cells(Rows.Count, 2).End(xlUp))
    rng.AutoFilter 1, "*Amazon*", xlOr, "*Golden State*"
    rng.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
    rng.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.ClearContents
    sh1.AutoFilterMode = False

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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