+ Reply to Thread
Results 1 to 6 of 6

VBA macro to find specific text, then copy into a new WB data underneath such text

  1. #1
    Registered User
    Join Date
    09-12-2017
    Location
    west coast
    MS-Off Ver
    2010
    Posts
    3

    VBA macro to find specific text, then copy into a new WB data underneath such text

    Hello everyone,

    My knowledge in VBA is at the beginner's stage, but I managed to create a VBA macro that would prompt the user to browse and select specific excel sheets. Then the macro would select specific cells and copy them into a new workbook/sheet (for example, take cell B:2 and copy it into A2, etc). The problem is that I started noticing that the origin document had new cells added to them, but since I had specific cells selected to be pasted, the macro makes a mess and doesn't copy the number of cells that I need to have transferred.

    Essentially, this is what I need:

    Capture.JPG

    I need the macro to first find the cell labeled "Data 1" and then copy all the green text into the destination sheet as show. Then the do same thing for Data 2 and Data 3. Cells without color should be ignored, and the item column should contain the name of the origin's sheet. The origin sheet can also have up to 50 rows worth of information, so ideally I would like to have the macro run and select only the rows that have information in them while searching through the entire sheet.

    Does anyone have a macro that would help me achieve that?

    Many thanks in advance!

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: VBA macro to find specific text, then copy into a new WB data underneath such text

    Probably nothing exactly suiting your needs but it will be rather simple macro to write from the scratch. To facilitate writing please attach a SMALL sample Excel workbooks (10-20 rows of data is usually enough) (one source file and template for destination file). Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Best Regards,

    Kaper

  3. #3
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: VBA macro to find specific text, then copy into a new WB data underneath such text

    Hi
    Considering you are learning VBA, I've included a Code using simple structures to achieve your goal
    According to your sample, I assume "full row blanks".
    Attached is a working flie

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Barriers are there for those who don't want to dream

  4. #4
    Registered User
    Join Date
    09-12-2017
    Location
    west coast
    MS-Off Ver
    2010
    Posts
    3

    Re: VBA macro to find specific text, then copy into a new WB data underneath such text

    Thank you so much Kaper! Thank you also for the tips. Sadly all the information contained in the Excel sheet is confidential, so "bla1, bla2, etc." was the safest way to go.

  5. #5
    Registered User
    Join Date
    09-12-2017
    Location
    west coast
    MS-Off Ver
    2010
    Posts
    3

    Re: VBA macro to find specific text, then copy into a new WB data underneath such text

    This is PERFECT, vichopalacios! Many, many thanks for your help!

  6. #6
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: VBA macro to find specific text, then copy into a new WB data underneath such text

    Hi @tempforVBA

    You are welcome
    I'm glad it worked for you.

    Following Forum rules, if you got an answer to your problem , you should mark the thread as SOLVED.
    A nice way to say Thank you is clicking on the *Add Reputation star button, on the lower left corner of the post window.


    Vicho

+ 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] Find specific text within workbook's name, copy and paste it to specific cell
    By axtryo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-09-2017, 02:57 AM
  2. [SOLVED] VBA to Find Specific Text, then Copy range to next open cell under specific text
    By Remotruker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2015, 10:59 AM
  3. Macro to Find Specific Text & Copy Row Values (Rid of Formula)
    By jrnewport1115 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2014, 10:25 AM
  4. [SOLVED] Copy text files underneath each other
    By ditöle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-05-2013, 07:28 AM
  5. Replies: 0
    Last Post: 11-26-2012, 05:51 PM
  6. Replies: 0
    Last Post: 11-06-2012, 06:44 PM
  7. Replies: 3
    Last Post: 01-04-2010, 03:24 PM

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