Ed: this code below works. I made 3 chages

I added these two statements
Set wk = Sheets("wk")
Set wk2 = Sheets("wk2")
I fixed this statement that was creating errors
wk.Range("A16:J16").Select

I didn't know what y was in the original code.


When I debug code I put a break point in VBA on the first statement and step
through the code pressing F8. I've noticed people have On Error statements
in the code and don't realize there are errors in the code. The code doesn't
get executed. I think this is the case in your situation. Just don't modify
code without stepping through the code and testing it. Especialy when you
have On Error statements that skip executing all the code when there are
errors in the code.


Sub testcond()
Set wk = Sheets("wk")
Set wk2 = Sheets("wk2")
wk.Activate
wk.Range("A16:J16").Select
Set rng = Selection
Set rng = Intersect(rng, rng.SpecialCells(xlCellTypeVisible))
rng.Copy

With wk2
.Activate
.Range("A1").Select
Selection.PasteSpecial Paste:=8
.Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:= _
False, Transpose:=False
.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:= _
False, Transpose:=False
End With


End Sub

"Ed" wrote:

I have the following code snippet that pastes over a copied range into
a new worksheet. The problem is that conditional formatting in the
first range has set some cell colors, and those colors are not being
transferred over in the copy.

The CF has two conditions; some of the copied cells have met the first
condition only which sets the cell orange, and some both the first and
the second conditions which sets the cell red. The conditions are
based on vlues in cells which do not get copied over.

Any cell which in the original worksheet is either orange or red gets
copied only as orange. Is there anything I can add which copies over
exactly what I see?

Ed

PS - I also have a problem with some of the values which are dates.
Because the action being reported has been completed, an IF formula
has set the date to 0, and zero values are not shown. When the new
workbook pops up to be copied into, though, these blank dates show up
as 1/1/1900. Any remedies?

wk.Activate
wk.Range("A16:J" & y).Select
Set rng = Selection
Set rng = Intersect(rng, rng.SpecialCells(xlCellTypeVisible))
rng.Copy

With wk2
.Activate
.Range("A1").Select
Selection.PasteSpecial Paste:=8
.Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
.Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With