+ Reply to Thread
Results 1 to 16 of 16

Find String, copy entire row, paste another workbook next available row.

  1. #1
    Forum Contributor
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    102

    Find String, copy entire row, paste another workbook next available row.

    Hey,

    I found this vba that looks for a certain string "*ori*" in the M column on a certain sheet and then pastes it into another sheet, but on its matching row.
    In the end id like it to look for the string and copy and paste the entire row to another open workbook, lets say "Master Book" but on the next available row.

    Please Login or Register  to view this content.
    Any help would be much appreciated!

    Cheers

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find String, copy entire row, paste another workbook next available row.

    Place this macro in the source workbook and make sure that both workbooks are open. Change the destination workbook name (in red) and the destination sheet name (in blue) to suit your needs. The macro assumes you have headers in row 1 and your data starts in row 2.
    Please Login or Register  to view this content.
    Last edited by Mumps1; 08-12-2019 at 11:20 AM.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    102

    Re: Find String, copy entire row, paste another workbook next available row.

    Hey Mumps1!

    Thanks for the response

    The sheet that was created, wasnt made by me and it has all kinds of titles and other information above the table, with merged cells etc, so a filter wouldn't work, thats why i found the previous macro semi useful.

    When i did remove all of the above info so only title headers were at the top, as a test, i got a "400" error anyway unfortunately. Is there a way, similar to my previous one that it can just search for the word?

    Quote Originally Posted by Mumps1 View Post
    Place this macro in the source workbook and make sure that both workbooks are open. Change the destination workbook name (in red) and the destination sheet name (in blue) to suit your needs. The macro assumes you have headers in row 1 and your data starts in row 2.
    Please Login or Register  to view this content.
    Cheers
    Last edited by marcosis; 08-13-2019 at 03:54 AM.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find String, copy entire row, paste another workbook next available row.

    First of all, merged cells create serious problems for Excel macros. You should avoid them at all cost. I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Could you attach a copy of your file? If the workbook contains confidential information, you could replace it with generic data.

  5. #5
    Forum Contributor
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    102

    Re: Find String, copy entire row, paste another workbook next available row.

    I've thought of another way, which seems to be really simple, it'd be 2 macros, but we can call the second one after the first im guessing, or if not possible, i dont mind them being attached to seperate buttons.

    This one searches an entire folder of workbooks for a keyword and returns the filename, the sheet name and the cell number.

    Is there a way, with those 3 pieces of information to copy the row from the referenced cell.

    It populates a table where the headers are..........

    Workbook - Worksheet - Cell - Text in Cell

    Could a macro then use this information and pull across the row that it appears on.

    The code for the macro im referencing at the moment is

    Please Login or Register  to view this content.
    Thanks again

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find String, copy entire row, paste another workbook next available row.

    That's a bit of "overkill" and would slow down the macro and complicate things. Are you now saying that you want to search column M in all the sheets in multiple workbooks for a particular string which can vary and copy each matching row to a Master workbook? If this is correct, it would make it easier to test possible solutions if you could attach a copy of at least one of your source files and a copy of your master file.

  7. #7
    Forum Contributor
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    102

    Re: Find String, copy entire row, paste another workbook next available row.

    See attached worksheet.

    The master sheet is just a blank workbook, so just open a new workbook.

    Theres multiple worksheets in the workbook, but this is the sheet id be looking in "Sales To Be Processed".

    If you look in column M theres item codes that contain "ori"

    Its these rows that would need extracting to a new workbook.

    If possible, it would do this search for all of the workbooks in a specified folder and return all results to one sheet in a workbook, one under each other.

    Even if you can complete part of this process or think of another way of doing it, that would be great.

    Cheers
    Attached Files Attached Files

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find String, copy entire row, paste another workbook next available row.

    Run this macro from your Master workbook.
    Please Login or Register  to view this content.
    Last edited by Mumps1; 08-13-2019 at 10:04 AM.

  9. #9
    Forum Contributor
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    102

    Re: Find String, copy entire row, paste another workbook next available row.

    Wow!

    Thats incredible!

    3 things.

    1. Is there any way the search term could be an input box, incase they wanted to search for something else?
    2. If a sheet is password protected, can you add in a line to unlock it?
    3. Where did you start to learn all of this. Id love to be able see a problem, in the future, and know how to start working out a solution.

    Quote Originally Posted by Mumps1 View Post
    Run this macro from your Master workbook.
    Please Login or Register  to view this content.

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find String, copy entire row, paste another workbook next available row.

    Try this macro. Change the password (in red) to suit your needs. You will notice one line of code unprotects the sheet to allow data manipulation. Normally, you would protect the sheet again at the end of the macro but in your case it's not necessary to re-protect it because the file is closed without saving.

    Please Login or Register  to view this content.
    I found that the best way to learn VBA on your own is to use on-line tutorials, searching the web for specific areas of interest and following forums such as this one. A good place to start is the very first post in this Forum. The volunteers on sites such as this one are invaluable at providing help. I would suggest you keep a file of codes that you find useful and in this way you can build up a library that you can refer to. A lot is also trail and error. The more you practise, the better your get at it. The following links may help:
    https://www.mrexcel.com/forum/lounge...ml#post5235342 (created by hiker95)
    http://www.snb-vba.eu/index_en.html

  11. #11
    Forum Contributor
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    102

    Re: Find String, copy entire row, paste another workbook next available row.

    Thanks for all your help!

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find String, copy entire row, paste another workbook next available row.

    You are very welcome.

  13. #13
    Forum Contributor
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    102

    Re: Find String, copy entire row, paste another workbook next available row.

    Hey Mumps1,

    Sorry to bother you one last time! I PROMISE! haha

    Is there a way it can return the file name that it found the string in?

    Maybe in column A or anywhere easily viewable to be honest!

    Cheers

  14. #14
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find String, copy entire row, paste another workbook next available row.

    Try:
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    102

    Re: Find String, copy entire row, paste another workbook next available row.

    Works perfectly!

    But i didnt have to tell you that. You know how good you are!

    Haha

    Thanks mate!

  16. #16
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Find String, copy entire row, paste another workbook next available row.

    My pleasure.

+ 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. How can I copy and paste an entire workbook?
    By Jordon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2017, 05:35 PM
  2. Search Item, Find, then Copy Paste Entire Row
    By kjbcox15 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-24-2013, 12:41 PM
  3. Copy and paste entire row based on text in row A to new workbook.
    By floydian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-16-2013, 04:53 PM
  4. VBA Find first non-blank cell, then copy and paste the entire row
    By Misterturtle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2013, 12:49 AM
  5. [SOLVED] Search multiple worksheets for Text String, copy and paste entire row into new worksheet
    By spulliam2000 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-15-2013, 07:52 PM
  6. Replies: 4
    Last Post: 10-25-2012, 05:09 PM
  7. Look in entire column and copy/paste data as new workbook
    By uncleslinky in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-18-2012, 05:11 AM
  8. Search, copy and paste entire row into new workbook
    By wildcats0625 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-06-2010, 11:14 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