+ Reply to Thread
Results 1 to 6 of 6

A macro that will search a column for a value and copy the value to another sheet.

  1. #1
    Registered User
    Join Date
    05-29-2015
    Location
    Birmingham
    MS-Off Ver
    2003
    Posts
    4

    A macro that will search a column for a value and copy the value to another sheet.

    Hello,

    I'm new to creating macros and I've been playing around trying to make one for hours without any luck.

    I have two sheets on my Spreadsheet. "Start" and "Register"

    What I need the macro to do is as follows -


    Search column B on sheet "Register" for a value that is entered into cell U7 on sheet "Start"

    If no value is found then a message displays 'No value found'

    If the value is exactly the same as the value entered on U7, then the macro copies the value on sheet "start" in U8. And copies that value into column "P" in the exact same row as the value "U7" that was found on sheet "Register"


    So this is how I dream it will work -

    On the "Start" sheet I enter a serial number in U7 for example 001, and a date returned (e.g. 29/05/15) in cell U8. I then click the update button which is hopefully linked to the above macro.

    When I click that button the macro searches column B on sheet 'register' for serial number 001. When 001 is found the macro will copy the date from cell U8 on sheet 'start' (29/05/15) and copy that date into column "P" on sheet 'Register' in the exact same column as 001 was found. (So if 001 was found on sheet 'Register' in cell B12, the macro will copy the date in U8 on sheet 'Start' into cell P12 on sheet 'Register'.

    If someone could help me on the above issue you would be a lifesaver!

    As we have 1000s of serialised items than are lent out it would save days going through records if a macro could find and populate the entered return date.

    I hope the above makes sense.

    Regards,

    Jack


    This has been cross posted here -

    http://www.mrexcel.com/forum/excel-q...ml#post4170481

    Not answer has yet been received, I will indicate in this post if an answer has been submitted on the other forum.
    Last edited by Coates1991; 06-01-2015 at 02:58 PM.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: A macro that will search a column for a value and copy the value to another sheet.

    What you need is simplicity itself.

    A sheet specific macro on "Start" sheet to monitor U7 and U8
    If U7 is a number and U8 is a date
    then the macro calls a subroutine

    The subroutine searches column B on sheet 'register' for serial number from U7
    If a match is found the macro saves the date from U8 into column P of the same row.

    You will need 2 macros because the sheet specific macro cannot alter another sheet.
    My Rules if you want my help:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: A macro that will search a column for a value and copy the value to another sheet.

    The sheet specific macro is:-

    Please Login or Register  to view this content.
    Right Click on the sheet name "Start" at the bottom of excel and select view code.

    Paste this code in the window that opens and then close that window.


    The subroutine needs to be placed in a normal macro module.

    Select Visual Basic from the developer menu and then select insert module

    Paste this code in the window that opens and then close that window.

    You are then set to go.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 05-29-2015 at 01:05 PM.

  4. #4
    Registered User
    Join Date
    05-29-2015
    Location
    Birmingham
    MS-Off Ver
    2003
    Posts
    4

    Re: A macro that will search a column for a value and copy the value to another sheet.

    Thanks mehmetcik.

    I tried this on my home computer and it worked fine, as I have the newest version of excel. But this morning I went onto my work computer hopeful minded, copied and pasted the relevant information and the macro didn't work?

    IS this because my company system runs excel 2003?

    Many thanks,

    Jack

  5. #5
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: A macro that will search a column for a value and copy the value to another sheet.

    It should have worked

    But you need to make sure the sheet names are as you described in your original post.

  6. #6
    Registered User
    Join Date
    05-29-2015
    Location
    Birmingham
    MS-Off Ver
    2003
    Posts
    4

    Re: A macro that will search a column for a value and copy the value to another sheet.

    My mistake! For some reason there was a space after my sheet 'register' which was putting it out.

    Works like a dream, thanks alot!

+ 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. Search for a word in the column and copy it to another column on the same sheet
    By Infernon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2013, 02:19 PM
  2. Need Macro to search from mater sheet and copy in another sheet based on Criteria
    By humacdeep in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-29-2012, 01:03 PM
  3. Search for Value in Closed Workbook and copy Column to Active Sheet Column BE
    By Winon in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-19-2011, 12:57 PM
  4. Macro to search the sheet name and copy and paste the data from a major sheet
    By salma2009 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2009, 02:11 AM
  5. Search for column heading and copy column onto another sheet
    By chrismann85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2008, 05:53 AM

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