Consulting

Results 1 to 5 of 5

Thread: HELP Getting data to final sheet based on highlighted cells!!!

  1. #1
    VBAX Regular
    Joined
    Jun 2019
    Posts
    45
    Location

    HELP Getting data to final sheet based on highlighted cells!!!

    Hello,
    In Sheet1, I have rows 3,4, and 5 that contain cells that are highlighted. The highlighted cells after column A and B show that the data is bad and should not be put in final sheet. Each highlighted cell is in a column and other named ranges corresponding to that same column will not go in the final sheet.

    I want to make a code that will pick only the columns in the named range set that corresponds to none of the highlighted cells ( I can have all of the named ranges pulled over even the ones in Sheet2 EXCEPT the named range called "POP") to bring to the final sheet called "Final_504", taking their data in column A and B along with them.

    I made the outcome manually in the final sheet to let you see what I am saying but I need to code it in a generic way. So if I was to do this with 500 data points, I could.

    Can anyone help me with this?
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,572
    See button in attached on Sheet1 near cell S1.
    Depends on your named ranges being properly set up (they don't all have to start in column A).
    Doesn't clear the destination sheet before writing to it.
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    277
    Location
    p45cal,
    For c = 3 To ... loop can be shortened to form:
        For c = 3 To RngColoured.Columns.Count
            If RngColoured.Columns(c).Interior.Color <> 0 Then
                For Each rng In RangesToCopyFrom
                    rng.Columns(c).Copy Destn
                    Set Destn = Destn.Offset(rng.Rows.Count + 1)    'A1
                Next rng
                Set Destn = Destn.Parent.Cells(1, Destn.Column + 1)
            End If
        Next c
    The Interior.Color property for a range with cells of different colors returns 0.

    Artik

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,572
    Quote Originally Posted by Artik View Post
    The Interior.Color property for a range with cells of different colors returns 0.
    Yes, that does shorten it considerably and it will work in this case.
    BTW, if all the cells are black, that too will return 0, but it doesn't matter here.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Jun 2019
    Posts
    45
    Location
    It worked!!!!!!!!!!!!!!!!!!!!!!
    Thank you guys!!!!!

Tags for this Thread

Posting Permissions

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