+ Reply to Thread
Results 1 to 10 of 10

Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

  1. #1
    Registered User
    Join Date
    02-23-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Question Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    Hello All,
    I have 3 Sheets "Data", "removed" & "excluded".
    In Sheet "excluded" In column "A" I have 50 Keywords but I am sure that it i will grow over time.
    What i want to do is to take one keyword at a time & find that keyword in "Data" & if it is found i want that entire row to "Cut" & Paste it in "next empty row in "Removed".
    In the Code I found on in this area the person posted

    For Example: If key word in sheet "excluded" Cell "A1" is "refresh", I want to find the word refresh in data. It Should not search exactly for the word "refresh", even if it is "Refresh Replaced" or "lease refresh" it should Cut those rows as well, As it contains the keyword in sting inside the cell.

    Once it is done with the first keyword it should move on to next keyword. I have been playing with this Script that copies from another post. The team here helped get the roes copied. I attempted modify the Code and it looks like it is removing some of the rows that it is finding but for some reason I am getting duplicates and I noticed that not all the rows are being removed for the "Data" see when it finds on the words listed in the "Excluded" sheet.

    I am taking no Credit for this code, I typically understand enough to tweak the code, But this one is stumping me.

    The logic I was using doesn't seem to be working. I replaced the .copy with Cut on and got an error. I attempted to change the first .copy the and make the second .copy switched it to .cut and noticed that it will simply keep copying the same row over and over again. last attempted was to not modify the code and just added a fn.EntireRow.delete right before then right after the loop but got errors as well.
    Any help on this is highly appreciated.
    Thanks in advance..


    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    Please attach the sample for testing.

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

    Re: Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    Maybe:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    02-23-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    Thank you for the help... I am getting an error when I run the code you provided.. Error: No-cells were found" then debug make reference to this section of your code.

    Please Login or Register  to view this content.
    I would like to have the Key Words scan the whole data sheet not just the first column.
    thanks for your help...

    I would upload a sample excel file.. but I am struggling to figure out how to do that

  5. #5
    Registered User
    Join Date
    02-23-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    just wanted to thank everyone for the help
    Last edited by brayboyce; 11-19-2015 at 07:14 PM.

  6. #6
    Registered User
    Join Date
    02-23-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    Sample.xlsx

    Figure it out here is a sample of the sheet...

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    Try the attached
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-23-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Post Re: Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    Ab33, thank you helping me attempt to resolve this task that I am stuck doing manually .... the "Data" Sheet Start with 55 items, and the "Removed" sheet Starts with "0" I took the "excludelist" and assigned them to each the rows in the "Data" sheet. when I run the script, it copies 55 rows but the data sheet is not reduced by 55 rows. My goal with your help is search the "Data" sheet and anytime one the words or phrases in the excludelist appears. I would like to have the Row that contains the word or phrase. moved to the "removed" sheet.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    Just add this line if you want to delete the copied rows from data sheet.
    RngDel.Delete

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    02-23-2012
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Cut Entire Row, paste it in next empty Row In Another Sheet, based cell values

    AB33, Thank, thank, thank... the script is working as I envisioned it to.. great, job you are true Guru. hopefully I will be able to return the favor someday.

+ 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] Paste Userform textbox values into next empty cell on a sheet
    By dubcap01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2015, 04:51 AM
  2. Copy and paste entire row to second sheet based on cell value
    By SilverMonkey in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-25-2015, 08:49 AM
  3. [SOLVED] Copy & paste entire row to different sheet based on cell entry
    By XxCMoneyxX in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-14-2013, 07:52 PM
  4. Extracting entire row to new sheet based on criteria, and then deleting empty row
    By phase321 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2013, 05:01 PM
  5. Copy Entire Row & Paste in First Empty Row On The Same Sheet
    By Emonk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2011, 11:41 AM
  6. based on Cell/Column content ,cut one sheet's values and paste it in other sheet?
    By mindpeace in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-25-2006, 08:33 AM
  7. based on Cell/Column content ,cut one sheet's values and paste it in other sheet?
    By mindpeace in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-24-2006, 10:33 AM

Tags for this Thread

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