+ Reply to Thread
Results 1 to 25 of 25

copy and paste containing certain words into same naming worksheet

  1. #1
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Arrow copy and paste containing certain words into same naming worksheet

    Hi all, I want a vba code for copy paste. My requirement as follows:

    I have a huge data containing column DATE, NAME, TEL NO. etc. I want to copy whole row containing NAME column like, "JOOILEE", "MALAN","JOSHI" etc. more than 150 different words which are not identical to each other. Totally altogether different. My name column is B. If the name is MALAN then the entire row data should copy and paste to new sheet namely with same name which already have MALAN sheet.

    I search on this forum and found some codes but could not make sufficient changes that I want.

    Thanks

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    How are you controlling the search process? Is MALAN the entire entry in a NAME cell, or is the name actually longer and MALAN is just part of the name?

    Do you already have a sheet for all the names you want to search so we could actually use the sheet names as the search criteria?

    One sheet at a time,
    1) get the sheet name
    2) Apply a filter the to master sheet column B to only display the rows that have that sheet name as part of the string in column B
    3) Copy all visible rows to the named sheet
    4) repeat with all named sheets
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Hi Jerry,
    I try to explain once again. I have data containing DATE, TEXT, NUMBER, NUMBER, NUMBER, NUMBER. Column B i.e. TEXT column is important. In this column there are different text which will be never duplicate. I want say MALAN is the text, copy this row with all its contains and send or copy and paste in the second row of MALAN sheet. Another example, say JOOILEE is other name, send all JOOILEE row to the second row of JOOILEE sheet. Like that there are more than 100 such names which are NOT DUPLICATE. This data is the first MAIN sheet.

    I get this excel file daily. In this names will not change but the changeable part is date and different numbers in other columns. I get this daily evening, and I have to copy this data to everybody's personal sheet ( recent data at top in row 2 ).

    If today is the 1st day of the month say 1st Dec 2013, after running macros it should copy at top row i.e. below header row 2. Again I will get fresh data next day i.e. 2nd Dec 2013, it should copy again at top row i.e. below header row 2. At the same time data of 1st Dec 2013, it should shift below one row i.e. at row 3. Like wise row 3 should shift below one row at row 4. Like wise row 4 should shift below one row at row 5.

    Maximum 20 days data should save after 20 days it should automatically delete row. Header is in row 1. From row 2 to row 21 after this i.e. row 22 should get deleted.

    Jerry, I hope you will get what I explain.

    Thank You very much.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    Yes, everything you indicated was clear from your first post. My questions remain unanswered, however.

    1) How are you controlling the search process? Is MALAN the entire entry in a NAME cell, or is the name actually longer and MALAN is just part of the name?

    2) Do you already have a sheet for all the names you want to search so we could actually use the sheet names as the search criteria?

  5. #5
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Jerry,

    your first answer is Yes, MALAN is the only NAME cell, no further extension.
    Second answer is no, vba should search, create sheet naming MALAN and paste data in it.

    Thanks in advance

  6. #6
    Forum Contributor
    Join Date
    03-28-2013
    Location
    *
    MS-Off Ver
    Excel 2010
    Posts
    226

    Re: copy and paste containing certain words into same naming worksheet

    can you post example workbook without senstive data.
    Give Feedback and Click(*)

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    Ok, I have a standard macro published for this, it creates worksheets (if needed) based on the content of your chosen column and pastes all the rows that match that row into the sheets.

    'SHEET1 TO MANY SHEETS
    Here's a macro for parsing rows of data from one sheet to many sheets named for the same values in a specific column.It not only can parse the rows, it can create the sheets if they are missing. There is a sample sheet there where you can test this out.


    in that macro, be sure to set the vCol to column 2 since your column is B.

    THere are two versions... I'd think you'd want PART 2 that would append new data to existing sheets.
    Last edited by JBeaucaire; 12-27-2019 at 04:11 AM.

  8. #8
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Jerry,

    First vba creats new sheets according to their available name and copy paste header and second row. The problem in first vba is that it does not shift row 2 data in row 3, therefore it overlap data every-time in row 2.

    Second vba does not create sheets according to their name. It copy data only those sheets already have. The problem in second vba is that it goes adding data new row below

    Both vba have minor problem. I want vba as follows: vba should create sheet IF DOES NOT ALREADY HAVE. Every time after running macro the data should copy in second row only. And those data already have in second row should shift in third row. After continues process of this, row 22 and afterwords data should permanently should be deleted.

    Regards.

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    OK, it was long overdue for me to update that sheet. I've replaced the TWO versions of the macro with one macro that does both things. You can get what you want from the newly published version.

  10. #10
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Jerry,

    Still small issue is there. The msg "If sheet exists already, add new data to the bottom?" Make some small correction here. If I click on Yes, the data add bottom. I want it to add new data at TOP ( Second row ). And shift already having row 2 data in third row. I think it should insert new blank row below header and insert new data there, then already having row 2 data will automatically shift to row 3.

    Regards.
    Last edited by watermasa; 12-23-2013 at 09:15 PM.

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    You would change this:
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet


  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    THat was an interesting response.

  14. #14
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Yes, because I confused and gone mad. I explain before several times. After adding NEW LINE, it stop coping, pasting, adding sheets. Therefore REMOVE ADDED NEW LINE and kept as it was before addition of line, but no change. It stop coping, pasting, adding sheets. Even it do not work as it was before.

  15. #15
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    I understand your need, your request, the frustration I assume is the result of you not being all the way there yet. Meanwhile, I am. The solution given works, and inserts new data at the TOP of the existing sheets, just like you wanted. Here's a video demonstrating:

    http://screencast.com/t/C8GQ2tXlA8

  16. #16
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Jerry its working fine now. One more small addition needed. DELETE entire data below row 22. I do not want to save data below this row. The file will become heavy then.


  17. #17
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Jerry, one more vba needed:-

    I get raw data in excel containing column A to O and in more than 20000 rows. I want to delete unwanted data. Column A is a text column. The text are 4 i.e. FUTIDX, FUTSTK, OPTIDX & OPTSTK. Column C having date.

    I want, when I run vba, it should ask me to which option from column A want to keep and which one want to be deleted. Say when I want FUTIDX & FUTSTK, the other two OPTIDX & OPTSTK containing data row should get delete. After this next window will ask me which date data need and which one should be deleted. Here the dates should choose from column C.

    After this two proper & successful selection all data columns from K to O should be deleted. I do not want column K to O.

    Regards.




    Jerry,
    I tried this: https://sites.google.com/a/madrocket...delete-special
    from your site. But takes lots lots of time to delete rows. Your suggestion.
    Last edited by JBeaucaire; 12-27-2019 at 04:09 AM.

  18. #18
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    Here's a variation on that macro from my website that will attempt to delete the rows enmasse rather than one at a time, should be noticeably faster on larger datasets:

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 12-25-2013 at 05:11 AM.

  19. #19
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: copy and paste containing certain words into same naming worksheet

    Quote Originally Posted by JBeaucaire View Post
    THat was an interesting response.
    You missunderstoond that Jerry!

    The meaning of that was Merry Christmas to you and your family!
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  20. #20
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    Actually, in Excel 2010 and beyond, there is no longer a limit on the number of "areas" within a single defined range, so this would really delete them all at once:

    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Jerry,

    Still one small problem remaining in my first vba. As I want to delete or no need to save data below row 22.


    I already added *reputation, but I can't see where is has gone.

  22. #22
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    One line of code that will delete all rows of data below row 22, you can add into your macro at the point where you want it to occur, near the top if you want it to happen first, or near the bottom if you want it to happen last.

    Please Login or Register  to view this content.

  23. #23
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Above code given Delete Rows() For 2003-2007. I want as given below.

    First window will ask me for first column only , "which rows containing text ABCD I want to keep", not delete.
    Second window will ask me for third column only, " which row containing date say 30 dec 2013 I want to keep", not delete.

    Above other than do not want keep should get deleted.

    Lastly it should ask me which columns from ___to___should get delete.

    Regards.

  24. #24
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy and paste containing certain words into same naming worksheet

    I believe your original question has been answered and now feature drift is taking over.

    This last post of yours sounds so far astray from the original post I'm going to suggest you close this thread (above use Thread Tools > Mark as Solved) and start a new thread for these new ideas.

  25. #25
    Registered User
    Join Date
    12-21-2013
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2016
    Posts
    46

    Re: copy and paste containing certain words into same naming worksheet

    Thanks Jerry,
    My original question has been solved.

    Regards.

+ 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. Find 2 words and copy and paste everything in between
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-17-2013, 06:33 PM
  2. Search for multiple words and copy/paste rows
    By Excelcreep in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2010, 08:28 PM
  3. add sheet naming copy paste
    By herukuncahyono in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2010, 02:31 AM
  4. [SOLVED] copy/paste words in cell can't be read on screen
    By MO in forum Excel General
    Replies: 2
    Last Post: 04-28-2006, 10:55 AM
  5. How to copy & paste excel charts to Words as Windows Metafile
    By xppuser in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-06-2006, 11:20 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