Paste special

[dpaton05]

Is there a paste special property for the pasted text to be only a date, without formatting, such as background etc.

I have:

With Worksheets(Combo).Cells(Lastrow, 1)
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With

but it copies formatting and if I remove the last line, it writes a strange number that is nothing like a date. I don't want it to copy the formatting though. Is there a way to do this?

 

[MARK858]

Change the dd/mm/yyyy to suit

    With Worksheets(Combo).Cells(LastRow, 1)
        .PasteSpecial Paste:=xlPasteValues
        .NumberFormat = "dd/mm/yyyy"
    End With

Btw, the strange number is the number of days from 1st January 1900.

 

[dpaton05]

But how do I make the 4th value a price with a dollar sign? The value that is in J5 on the original sheet but pasted into column D on the new sheet.

 

[MARK858]

What 4th value? What price? you have only mentioned a date, and it is pasting to column A not D.

 

[dpaton05]

Sorry if I wasn't clear. The date is in column A, then there are two names in columns B and C and a price that is in column D.

I tried to modify the code to make the value in column D a price but it wouldn't work.

Sub cmdCopy_Click()


Application.ScreenUpdating = False
'Modified  10/25/2018  10:15:34 PM  EDT

Dim Lastrow As Long
Dim Combo As String

    Combo = Worksheets("Home").Range("Q5")
    Lastrow = Worksheets(Combo).Cells(Rows.Count, "B").End(xlUp).Row + 1
    Worksheets("Home").Range("a5:c5").copy


    With Worksheets(Combo).Cells(Lastrow, 1)
        .PasteSpecial Paste:=xlPasteValues
        .NumberFormat = "dd/mm/yyyy"
    End With
   
    With Worksheets(Combo).Cells(Lastrow, 4)
        .PasteSpecial Paste:=xlPasteValues
        .NumberFormat = "$#,##0.00;[Red]$#,##0.00"
    End With
    
    
        
    Lastrow = Sheets(Combo).Cells(Rows.Count, "B").End(xlUp).Row
    Worksheets("Home").Range("j5").copy
    Worksheets(Combo).Cells(Lastrow, 4).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[MARK858]

You are pasting the same 3 cells into 2 separate cells so both the cell in column A and the cell in column D will both get the same value/date from Worksheets("Home").Range("a5").
That can't be right surely

 

[dpaton05]

What would be the code Mark as I am still learning to code and most of that code was just copied and pasted?

 

[MARK858]

I can't answer that as I don't know which of your cells get copied to which cells, not psychic I am afraid.

 

[dpaton05]

It is all working now. I just am having a problem with some code to get the value in column J to be currency format.

I have:

    With Worksheets(Combo).Cells(Lastrow, 4)
        .PasteSpecial Paste:=xlPasteValues
        .NumberFormat = "$#,##0.00;[Red]$#,##0.00"
    End With

This is all my code:

Sub cmdCopy_Click()


Application.ScreenUpdating = False
'Modified  10/25/2018  10:15:34 PM  EDT

Dim Lastrow As Long
Dim Combo As String

    Combo = Worksheets("Home").Range("Q5")
    Lastrow = Worksheets(Combo).Cells(Rows.Count, "B").End(xlUp).Row + 1
    Worksheets("Home").Range("a5:c5").copy


    With Worksheets(Combo).Cells(Lastrow, 1)
        .PasteSpecial Paste:=xlPasteValues
        .NumberFormat = "dd/mm/yyyy"
    End With
   
    With Worksheets(Combo).Cells(Lastrow, 4)
        .PasteSpecial Paste:=xlPasteValues
        .NumberFormat = "$#,##0.00;[Red]$#,##0.00"
    End With
    
    
        
    Lastrow = Sheets(Combo).Cells(Rows.Count, "B").End(xlUp).Row
    Worksheets("Home").Range("j5").copy
    Worksheets(Combo).Cells(Lastrow, 4).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

It all worked until I tried to make the price put into column J to have currency format.

 

[MARK858]

It all worked until I tried to make the price put into column J to have currency format.

With Worksheets(Combo).Cells(Lastrow, 4)
.PasteSpecial Paste:=xlPasteValues
.NumberFormat = "$#,##0.00;[Red]$#,##0.00"
End With

Column 4 is D not J.