+ Reply to Thread
Results 1 to 5 of 5

Match criteria between sheets and copy cell values to existing record in master sheet

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    crewe, england
    MS-Off Ver
    2013
    Posts
    36

    Match criteria between sheets and copy cell values to existing record in master sheet

    I am trying to consolidate my data into one file and to do this need a way to add additional information to my DataStorage sheet with additional information held elsewhere. I am asking to have this extracted with the date attached to the records as shown in my sheet called IfsChecking. Rather than have someone laboriously copy the values in with the chance of data error; I was hoping to automatically copy the required information contained within cells C:H from the IfsChecking sheet to the cell range AK:AP in the DataStorage sheet, where the date and the WHS No. matching within each sheet.

    The process will probably be performed either daily or once a week pulling data across, however once it has been performed for a day or range of days. However it is envisaged that there will be over 36,000 row entries within a calendar year into the DataStorage sheet so concerned regarding time taken to loop through records.
    I have attached a sample workbook with an example of what I am trying to do, is there anyone who can help
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Match criteria between sheets and copy cell values to existing record in master sheet

    Try the attached.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-04-2012
    Location
    crewe, england
    MS-Off Ver
    2013
    Posts
    36

    Re: Match criteria between sheets and copy cell values to existing record in master sheet

    AB33,

    Many thanks for the code, its absolutely bang on I have done a fair amount of testing and its works great . I don't fully understand it but ordered a couple of books on vba. inc, one for dummies to get to grips with it. Surprisingly I made a tweak and it worked lol. I have highlighted what I changed in the code as needed to prevent clear contents on columns to the right of QA and added another input sheet and got the data to copy across as well. I added code to the command buttons on each sheet to execute your code, as I couldn't get it to work when tried to insert it to the buttons directly. Is this something me being a novice and not understanding or does the code need to be within the module to be able to work? I have attached a copy of the file with the tiny bit I did so at least it shows some effort on my behalf.

    Please Login or Register  to view this content.
    Again thank you for supplying the solution

    Cheers

    Mark
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Match criteria between sheets and copy cell values to existing record in master sheet

    There are many ways of clearing the columns, but I have changed to make it simple for you to adjust it.
    There are 2 types of controls where you can attach the code to. The simples one is to create a form and attach the code to that form, so when even you click the form the code runs as I have done on column AU(Attached).
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-04-2012
    Location
    crewe, england
    MS-Off Ver
    2013
    Posts
    36

    Re: Match criteria between sheets and copy cell values to existing record in master sheet

    Thanks for the adjustment for clearing the columns, that's great and a lot clearer. I see how you have done that with the form , something else learnt.

    Many thanks

    Mark

+ 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. Replies: 9
    Last Post: 05-27-2015, 06:28 AM
  2. Trying to Pull data from Master Sheet onto New and Existing Sheets
    By fjlaff in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-13-2014, 09:20 AM
  3. [SOLVED] Copy paste values from sheets into one master sheet
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-20-2013, 10:52 AM
  4. Copy data from master sheet to other sheets if criteria is met.
    By teachaman in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-30-2012, 06:43 AM
  5. Copy rows from master sheet to other sheets based on criteria
    By spikeysas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-07-2011, 04:28 AM
  6. copy cell from multiple sheets to master sheet
    By ciprian in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-16-2011, 08:07 AM
  7. Copy Rows from Master Sheet to Child Sheets Base on Values
    By Traquair in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2010, 03:19 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