Hi y'all,

I have a button on sheet "Master" that calls a sub to copy the contents of
sheet "Master" to sheet "FitterSurvey"--values and formatting only.

Public Sub FS_CreateFitterSurvey()
'
' Copies data from Master sheet to Fitter Survey sheet
'
On Error GoTo ErrorHandler

Dim Master As Worksheet
Dim FitterSurvey As Worksheet

Set Master = ActiveWorkbook.Sheets("Master")
Set FitterSurvey = ActiveWorkbook.Sheets("FitterSurvey_XXXXX")

Master.Activate
With Master
Cells.Select
Selection.Copy
End With

With FitterSurvey.Range("A1")
..PasteSpecial xlValues
..PasteSpecial xlFormats
End With

Master.Activate
Range("A1").Select
FitterSurvey.Activate
Range("A1").Select

End Sub


I need help with two problems:

1) some of the cells on the origin sheet are conditionally formatted to turn
pink based on value.

For example:
Cell B3 on sheet "Master"
Cell N3 on sheet "Person"

Cell B3's formula is:
=IF(ISBLANK('(Person)'!N3),"Please enter your title here",'(Person)'!N3)

Then, conditional formatting kicks in and based on a cell value of "Please
enter your title", it will turn B3 text color pink instead of automatic.

Then, after I use the sub above to copy the values and formats of the whole
page to "FitterSurvey". B3 on FitterSurvey remains pink due to the copied
over conditional formatting.

However, what I would really like is for B3 on FitterSurvey, whether it is
pink or black, to remain pink (or automatic) from now on, no matter what the
value (normal text color = pink or normal color = automatic).

Can this be solved either during the copy paste process, or after the paste,
to convert the normal color of the cell text to whatever it currently is
under conditional formatting?


2) The "Master" sheet will contain pictures or drawing objects, but they're
changing all the time. So, when I use the above macro to copy over the
contents, I would like for it to copy any objects over to the FitterSurvey
sheet, in the *exact same positions*. I have tried different things I've
seen in posts, but I can not get them to work correctly in my sub.

I adapted the following code from Peter T to my sub, but the pictures all
pasted starting in cell A1 for some reason. They were, however, correctly
positioned relative to each other.

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub