VBA Code For Copying Worksheet And Pasting Values In Another Is Comingling Formats In A Column

[DR RENO]

I have two worksheets in same workbook to which I'm copying one to another and pasting special values. Column T is mixing currency and number formats. New to VBA, so any help you could provide would be appreciated.

Private Sub CommandButton3_Click()


Dim lLR As Long

Application.ScreenUpdating = False

With Sheets("CAR Open Contract")
    lLR = .Range("A1:T" & .Rows.Count).End(xlUp).Row
        .UsedRange.Copy
End With

With Sheets("PriorDay").Range("A1")
    .PasteSpecial _
        Paste:=xlPasteValuesAndNumberFormats, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
End With

Sheets("CAR Open Contract").Range("A1:T" & lLR).Copy
With Sheets("PriorDay").Range("A1")
    .PasteSpecial _
        Paste:=xlPasteFormats, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
End With


Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

On the "CAR Open Contract" column "T" you only have one format?
Maybe it helps if you first clean the PriorDay sheet:

Private Sub CommandButton3_Click()
    Dim lLR As Long
    Application.ScreenUpdating = False
    
    Sheets("PriorDay").Cells.Clear
    
    With Sheets("CAR Open Contract")
        lLR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1:T" & lLR).Copy
    End With
    
    With Sheets("PriorDay").Range("A1")
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        .PasteSpecial Paste:=xlPasteFormats
    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[DR RENO]

On the "CAR Open Contract" column "T" you only have one format?
Maybe it helps if you first clean the PriorDay sheet:

Private Sub CommandButton3_Click()
    Dim lLR As Long
    Application.ScreenUpdating = False
    
    Sheets("PriorDay").Cells.Clear
    
    With Sheets("CAR Open Contract")
        lLR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1:T" & lLR).Copy
    End With
    
    With Sheets("PriorDay").Range("A1")
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        .PasteSpecial Paste:=xlPasteFormats
    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Unfortunately still having same issue. I have various formatting types in columns A - T. Date, General, Number, and Currency. Each of the of the other columns correctly displays the correct formatting. However column T displays several rows of Number, then several rows of Currency. This sequence goes on until the final value. Since it's the final column in the range, I thought that may be causing the issue so extended range to column U. Column U formats correctly, but T is doing the same issue.

 

[Fluff]

Does it make any difference if you swap the paste commands round

    With Sheets("PriorDay").Range("A1")
        .PasteSpecial paste:=xlPasteFormats
        .PasteSpecial paste:=xlPasteValuesAndNumberFormats
    End With

 

[DR RENO]

No still having same problem. What I did notice when trying your suggestion that I overlooked before is that Column N which is a Date format type is having the same issue as column T, although T is a Number format type. The each error in the same rows. N transitions from correct Date to rows of Number which is the same row sequence T is performing, except T transitions Number format type to Currency. There is no row pattern, as it will do 8-10 correctly and then 5 incorrect. The next 12 are correct then the next 6 or 7 are incorrect. Never had this happen before. I appreciate your guys help though.

 

[Fluff]

Not something I've ever encountered either.
Do you have any merged cells, or conditional formatting?

 

[DR RENO]

No nothing merged or any conditional formatting in the table. It's a 3rd party data extract with the only modifications being the formulas I've put in to additional table columns. 2 of the 6 columns I've added are having this issue, while the other 4 are displaying the correct format type. If I go to the PriorData sheet and reformat it will display correctly, but don't want to have User being forced to do that.

 

[DanteAmor]

In the cells you have numbers and dates or they can be texts that appear to be numbers or dates.