+ Reply to Thread
Results 1 to 3 of 3

Copy data from one sheet to another if cells are highlighted specific colours

  1. #1
    Registered User
    Join Date
    11-24-2020
    Location
    UK
    MS-Off Ver
    2010
    Posts
    5

    Copy data from one sheet to another if cells are highlighted specific colours

    Hi All,

    I am looking to copy highlighted cells into a different sheet, either by a formula or marco. The cells are highlighted by conditional formatting.
    In the example I've attached I want all red highlighted cells and the name of the person to be added to the 8 Weeks sheet. Same for the orange highlighted cells, I want all of the orange highlighted cells and the name of the person to be added to the 12 Weeks sheet.

    A sheet like this will be looked at a few times a month, so would it automatically update the sheets or would there need to be a button say to run a macro to search the sheet?

    Thanks
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Copy data from one sheet to another if cells are highlighted specific colours

    They're on a few ways that you can run automatic code inside of an Excel program. One of them is to write code behind the routine called workbook_open. the other option you have that I know of is to run a task in Windows task Manager or the event manager one of those two that opens the Excel program and targets the file location which would open the exact file and then the code behind the workbook open event I just mentioned would run automatically and at the end of that routine you could issue a closing statement so the workbook essentially closes itself automatically.

    To get a handle on the properties you need to know that detect whether cells are shaded in any given color and using the RGB function or strict hexadecimal codes to identify colorations from the color grid, you need to look up the property called interior. That is a property of the range object and of a couple other objects as well. And it has expensable dependency properties of color and a few other things so those are the things that you need to look up for detection and then copy and paste your values from one range to the other from sheet to sheet.

    for a universal technique of looking up any code in Excel that you need to read about from microsoft, always Google these words and the results will be given to you in the first five lines of the Google search:

    http://Www.Google.Com/search?q=excel....property_name

    and of course if you need to look up functions, methods, statements, or objects, then you would leave the property name portion of that web address out and just keep the function name portion and change it appropriately.

  3. #3
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Copy data from one sheet to another if cells are highlighted specific colours

    Welcome to the forum.
    I use a helper column with this formula in cell AP3 of Main Database tab and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then I use those values as row numbers in this other formula to list names in the 8 weeks tab, cell A3 copied to B3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then this array formula (Ctrl+Shift+Enter) in cell P3 copied across and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Please check file attached.

    Good luck!
    Attached Files Attached Files
    Last edited by Estevaoba; 11-26-2020 at 10:14 AM.

+ 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] Copy data from specific cells and columns not adjacent from sheet to another
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-10-2020, 03:38 PM
  2. [SOLVED] VB Code to copy highlighted data to another sheet
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-15-2018, 01:28 PM
  3. [SOLVED] Copy data from Specific Cells on one Sheet to Specific Cells on Another Sheet
    By catnam in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2018, 04:01 PM
  4. Trying to copy data and cells I have highlighted into new worksheet
    By ziggyztz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-30-2015, 09:52 AM
  5. [SOLVED] VBA or Macro to copy highlighted cells and paste into a new sheet
    By retroboy17 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-29-2014, 12:02 PM
  6. Copy the data in any active row in Sheet 1 to specific cells in Sheet 6
    By cti4sw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2013, 02:43 PM
  7. VBA - copy data from one sheet to Specific Cells
    By jcicero57 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2013, 12:14 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