Copy and Paste range to bottom of different worksheet and retain formatting?

[JazzSP8]

Hey All

I've got a perfectly fine and working Macro but I need to tweak it and can't work it out :-/

I need to be able to Copy and Paste a range of cells from one worksheet to the bottom of another in the same workbook, that's what this code does;

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    For y = 1 To LastCol
        If Cells(1, y).Value = "Products" Then
            Range(Cells(2, y), Cells(NewLastRow, y)).Copy Sheets("Final").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
            FoundPr = True
        End If
        If Cells(1, y).Value = "ID" Then
            Range(Cells(2, y), Cells(NewLastRow, y)).Copy Sheets("Final").Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
            FoundID = True
        End If
    Next y

The thing is that some of the cells it copies have background colours and I need those copying as well.

Is there a simple way I can do with that code?

Something like ".PasteSpecial xlPasteFormats" ?

Brain freeze on this one

 

[Fluff]

Is the cell fill done by conditional format?

 

[davesexcel]

Try pasteSpecial

        If Cells(1, y).Value = "Products" Then
            Range(Cells(2, y), Cells(NewLastRow, y)).Copy
            Sheets("Final").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
            Sheets("Final").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
            FoundPr = True
        End If

maybe even xlpasteall

 

[JazzSP8]

Not as far as I can tell

These are workbooks that have been completed by other people, they don't seem to have anything special about them but I don't know what has happened to them before I got them. I've just been tasked with combining them all into one worksheet

Thanks for the reply!

 

[Fluff]

In that case the cell colour should be copied along with the cell contents.

 

[JazzSP8]

In that case the cell colour should be copied along with the cell contents.

I thought that was the case, I've been properly confused with it!

The method suggested by davesexcel above also didn't work, I'd tried something similar to that already but I tried it anyway - Nope

Well. This is even odder than I thought then.

I'll see if I can see anything in the workbooks that could be causing weirdness, they've been out in the wild so anything could have happened with them.

(The whole searching for the column name thing is because people have messed with the templates they where given, adding and deleting columns willy nilly and so things aren't where they should be in most of the 65 workbooks I've to go through :-/ )

 

[Fluff]

If you have a look at Chip Pearson's site here there are some functions that might help to identify if it is CF

 

[JazzSP8]

Thanks Fluff, I'll take a look and see what is going on - Oh, the joys