PDA

View Full Version : [SOLVED] HELP Getting data to final sheet based on highlighted cells!!!



ytjjjtyj
08-19-2019, 02:21 PM
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? :(

p45cal
08-19-2019, 05:35 PM
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.

Artik
08-20-2019, 08:47 AM
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 cThe Interior.Color property for a range with cells of different colors returns 0.

Artik

p45cal
08-20-2019, 12:20 PM
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.

ytjjjtyj
08-22-2019, 10:28 PM
It worked!!!!!!!!!!!!!!!!!!!!!!
Thank you guys!!!!!