Consulting

Results 1 to 7 of 7

Thread: Find Text Within A Row, Then Copy Information Below That Cell To Second Worksheet

  1. #1

    Find Text Within A Row, Then Copy Information Below That Cell To Second Worksheet

    Iíve recently taken over an Excel workbook that uses VBA to find, copy, and paste information between two workbooks. Iím relatively new to Excel VBA coding, so Iím looking for any help that will reduce the vast number of lines, over 300, of redundant coding. I will be unable to provide the actual coding as it includes sensitive information and would take a lot of time to make the coding genetic. Here is what I want to accomplish:
    ∑ Find each instance of text within a row range
    ∑ Once I find a cell with text, I want to copy the cell and a 5x5 block of information, which is two rows below that cell, to another workbook
    ∑ The copied information needs to be appended to the last used row in the second worksheet

    Iíve searched the web for ideas on how to accomplish this. I can find snippets of coding that can do some parts, but I donít know how to consolidate and organize the coding into a single process.
    The attached file is a very raw example of the type of data Iím using. The three blocks of values on the source tab need to be pasted into the destination worksheet. Currently, in the destination tab is the format/result required.

    Any help is greatly appreciated.
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    408
    Location
    here you test this.
    Attached Files Attached Files

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,179
    Location
    @arnelgp, In your command_button module you use the following line

    "Dim rng As Range, last_row As Long, arr, r As Range"

    What does the arr represent?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    408
    Location
    Quote Originally Posted by Aussiebear View Post
    @arnelgp, In your command_button module you use the following line

    "Dim rng As Range, last_row As Long, arr, r As Range"

    What does the arr represent?
    it was just a preparation, so the arr and i think r is orphaned.
    at first i am undesided if i will go using array but decided not to.
    so that can be safely erase.

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,179
    Location
    Thank you
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6

  7. #7
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,179
    Location
    @kstoneman. Is this true?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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