Trying to paste values AND FORMATTING..

[JohnZ1156]

I have a range of cells which contain numbers, text and dates. Using VBA, I have "copied" the range. Now I make my target cell the active cell and want to paste the values with the formatting to this different sheet. It is pasting the values but not duplicating the text formatting or alignment formats (centered).

I wouild like the macro to copy the text font, date formats, number formats and alignment formats (centered).

Here's my code:

Sub testone()

    Worksheets("Invoice").Activate

    Range("D20").Select

    NumRows = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row - ActiveCell.Row + 1
'    MsgBox (NumRows)

    Range("P20:W" & NumRows + 19).Select
    Selection.Copy

    Sheets("Invoice List").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select

    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

End Sub

 

[6StringJazzer]

Two steps.

    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats

 

[JohnZ1156]

Hi 6StringJazzer

Thank you. I didn't realize that it required 2 steps.
My logic told me that "Paste:=xlPasteValuesAndNumberFormats" should work.
I guess I was wrong again.

Thanks for your help.
John.

 

[6StringJazzer]

NumberFormats is just for the number formats, that is, number of decimal points, whether it's a %, and so forth. You also need alignment and font formatting. PasteFormats includes all formatting.