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

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

Joel:

Thanks for the reply. That was only the snippet of code that does the
copy and paste. In the preceding code lines, wk and wk2 set fine as
worksheets in separate workbooks, and y is a row counter and doesn't
throw any errors when I run the code (either F8 or F5).

When stepping through, when I hit the line
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
the format that gets pasted into the conditional cells ignores
anything that was red and gives me only the orange.

I need to email this off.
I think I'm going to have to give up for now and just Copy as Picture
and paste into Word!

Ed

On Mar 19, 10:54 am, Joel wrote:
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- Hide quoted text -

- Show quoted text -