VBA Paste Date

[stevod]

All,

Need some help with date pasting, the sheet where the data comes from is a formula and the code that pastes is this

Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

When it pastes the date it cannot be read by a formula because it isn't in Date serial.
So the below 16/2/2020 can be read by a formula and sums if they are selected.
The 17/2/2020 was pasted with the vba code and doesn't work with a formula.

Can anyone help with this one, tried numerous things to get it to work now

 

[GWteB]

Your code should work. Are you sure your formula which evaluates to "16/02/2020" evaluates to a Date (ie 43877), instead of Text?

 

[stevod]

Yes it is formatted to "16/02/2020" but when it pastes through vba it loses the formatting and pastes as Text. i cant get understand why

i did find this https://www.myonlinetraininghub.com/fixing-excel-dates-formatted-text that may explain it better than me. i just cant figure out the easiest way to fix it

 

[GWteB]

Yes it is formatted to "16/02/2020" but when it pastes through vba it loses the formatting and pastes as Text. i cant get understand why

Neither do I because it should work. It's less neat but try this, stepping through it in VBE with F8 key

    Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[MARK858]

Are you sure that the cells being pasted to aren't formatted as Text before the PasteSpecial? also post in the thread the formula being copied from and your complete code.

 

[stevod]

the column is formatted to the date as the data being pasted in is just being pasted below (on the last line) and above there are 6 thousand lines of data.

The below code didn't work.

The only additional thing i see that may work is text to columns

    Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[stevod]

Sub Formulate()
Dim LR As Long
Dim i As Long

Application.ScreenUpdating = False

'##Sheet1## select pasted data with special visible cells
    Sheets("Sheet1").Select
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A4:M" & LR - 1).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
'##sheet 2## copy data down
    Sheets("Sheet2").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'copies down the empty cells in Column 1 & 2
    LR = Cells(Rows.Count, "C").End(xlUp).Row
        For i = 2 To LR
        If Cells(i, 1).Value = "" Then Cells(i, 1).Value = Cells(i - 1, 1).Value
        If Cells(i, 2).Value = "" Then Cells(i, 2).Value = Cells(i - 1, 2).Value
    Next
'copies the formulated data ready to paste into sheet 3
    LR = Range("C" & Rows.Count).End(xlUp).Row
    Range("P3:U" & LR).Select
    Selection.Copy
    
'##sheet3 pastes the copied data
    Sheets("Sheet3").Select
    Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'copies the formulas in the grey area to the end of the range in "F"
    Range(Range("G" & Rows.Count).End(xlUp), Range("A" & Rows.Count).End(xlUp).Offset(, 6)).Resize(, 19).FillDown

        Application.ScreenUpdating = True
        
'Deletes the data and refreshes pivot table

    Sheets("Sheet2").Select
    LR = Range("C" & Rows.Count).End(xlUp).Row
    Range("A3:K" & LR).Select
    Selection.ClearContents
    Range("A3").Select
    Sheets("Sheet1").Select
    Columns("A:I").Select
    Range("A2").Activate
    Selection.ClearContents
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.UnMerge
    Sheets("Searchable").Select
    ActiveWorkbook.RefreshAll



End Sub

 

[stevod]

I think ive fixed it with this formula on sheet 2 before it pastes the data on the 3rd.

=IFERROR(DATEVALUE(N1),"")

 

[GWteB]

the column is formatted to the date as the data being pasted in is just being pasted below (on the last line) and above there are 6 thousand lines of data.

Right now you are talking about the target column, but regardless of its formatting your code should work and mine also, in two steps instead of one step. Did you use the F8 key to see what's happening after step one? What's the formula in the source cell to be copied and pasted?

 

[MARK858]

Right now you are talking about the target column, but regardless of its formatting your code should work and mine also, in two steps instead of one step.

stevod was answering one of my questions from post 5

Anyway stevod appears to have it solved now that the source formula has been amended (which is why I also asked to see the formula in post 5).
The code could do with all that selecting being removed but if it isn't causing stevod any issues then it isn't a problem for this thread.