+ Reply to Thread
Results 1 to 9 of 9

Using macro to find and then copy and paste data

  1. #1
    Forum Contributor
    Join Date
    09-21-2007
    Posts
    196

    Red face Using macro to find and then copy and paste data

    I need to create a macro to find a text string and then copy it and its associated value to a separate sheet

    So in the example I want it to find 'Pig' on sheet 1 and copy and paste it on sheet 2 and also copy the value (23) and paste that on sheet 2 as well.

    I need it to be a "find" as I want to run it on other sheets as well but pig may be in a different row than it is is on sheet 1.

    I hope this makes sense and any help would be gratefully received.

    Many thanks,

    Jack
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Using macro to find and then copy and paste data

    You didn't tell us what you wanted to do with the number once you found it so, I put it in a message box.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    09-21-2007
    Posts
    196

    Re: Using macro to find and then copy and paste data

    You didn't tell us what you wanted to do with the number once you found it so, I put it in a message box.
    Sorry What i meant was when it find the name and value then it copies and pastes the name and value onto sheet 2 as in the example sheet please. So i have the Start part of sheet 2 and I want to run the macro so it creates the end part by finding those names and values from the sheet 1

  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: Using macro to find and then copy and paste data

    Try:
    Please Login or Register  to view this content.
    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.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Using macro to find and then copy and paste data

    And here is my hat in the ring. The way this code is set up, it looks at the current sheet (whatever that is) and reports it to Sheet2.

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    09-21-2007
    Posts
    196

    Re: Using macro to find and then copy and paste data

    Many many thanks for this. If i didnt want the message box and just wanted it to copy the name and amount for say Pig what would I need to remove from the macro to get rid of the message box and where would I need to have the required word in the macro.

    I hope this makes sense. Many many thanks in anticipation

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Using macro to find and then copy and paste data

    The message box should only appear if the animal name is NOT found on the sheet.

    if you don't want this to show then comment out this line
    Please Login or Register  to view this content.

    The animal name is passed to the FindAnimal program by another program. In this case, Sub TEST()

  8. #8
    Forum Contributor
    Join Date
    09-21-2007
    Posts
    196

    Re: Using macro to find and then copy and paste data

    Quote Originally Posted by dflak View Post
    The message box should only appear if the animal name is NOT found on the sheet.

    if you don't want this to show then comment out this line
    Please Login or Register  to view this content.

    The animal name is passed to the FindAnimal program by another program. In this case, Sub TEST()
    Many thanks dflak. when I try and run the macro i get a compile error: Sub or function not defined. It seems to be regarding the LastCell (ActiveSheet.Name) part.

    Am I doing something wrong?

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Using macro to find and then copy and paste data

    And here is that missing piece of the puzzle.
    Please Login or Register  to view this content.

+ 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. Macro to find and copy/paste data
    By cory0789 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2015, 04:58 PM
  2. [SOLVED] Macro to find and copy/paste invalid data
    By jamiem4 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-20-2014, 06:26 PM
  3. [SOLVED] Macro to find data in source workbook and copy paste to target workbook
    By D.Lovell in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-23-2014, 06:21 AM
  4. Find specific Data copy and paste into Cell Macro VBA
    By kunmateo93 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-06-2014, 10:17 AM
  5. [SOLVED] Macro to Find, copy, paste, find, copy, paste, find, copy, paste, u get the picture....
    By westsoldman in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-12-2012, 01:12 AM
  6. [SOLVED] Macro to find SKU in column; then copy data in row and paste special (values)
    By Starkey in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-13-2012, 06:05 AM
  7. macro to find copy and paste
    By lukn4it in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2007, 11:37 PM

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