colour copy, vba colour copy, conditional formatting problem between two worksheets

[MrSak87]

ey experts,

This one is difficult to explain

1)On sheet1 after running a macro from an imported excel file, I have a single row with 5 columns which gives me an analysis of the data I've imported. From this analysis a simple conditional formatting flags up three possibilities and colours them: yellow, purple or leaves blank.

2)I then have another macro which copies this row into another worksheet (sheet2).

3)I then repeat step one which puts the results of the analysed data into the same row as before (sheet1).

4)I then repeat step two which pastes this row of data BELOW the one beforehand in (sheet 2)

This goes on and on I literally have hundreds of rows

The catch is the conditional formatting will change every time I import a new piece of data so which ever way I paste it into sheet 2 the formatting is wrong. Is there a way in VBA or otherwise to copy the colours generated by the conditional formatting and paste them in permanently so they won't be altered by the next file I import?

I'm also using excel 2003...

Thanks for any help

 

[mole999]

only successful way (that I have used) is to paste into word, then back into excel. I've never seen a better working answer

 

[MrSak87]

It's just a simple copy - paste job

Sub analys()


Range("A41:w41").Select
Selection.Copy
Sheets("Analysis").Select
Range("A2").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

End Sub


Thank you for the reply!

 

[mole999]

so you have it working now?

 

[MrSak87]

No, if I copy the formats the conditional formatting will mess up when I import the next file. I somehow need to copy the colours given by the conditional formatting. and paste them permanently with the data on the next sheet. So that when I import the next set of data these colours are unaffected

The manual way is to see that E41:G41 are yellow, then to change onto the work sheet and manually change the colours for those rows before importing. The chances of manual error here are high which is what I'm trying to avoid!