VBA Paste Date

stevod

Board Regular
Joined
Aug 21, 2013
Messages
67
All,

Need some help with date pasting, the sheet where the data comes from is a formula and the code that pastes is this
VBA Code:
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

1584218532739.png
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
707
Office Version
2013
Platform
Windows
Your code should work. Are you sure your formula which evaluates to "16/02/2020" evaluates to a Date (ie 43877), instead of Text?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
707
Office Version
2013
Platform
Windows
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
VBA Code:
    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

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,632
Office Version
365, 2010
Platform
Windows, Mobile
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

Board Regular
Joined
Aug 21, 2013
Messages
67
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

VBA Code:
    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

Board Regular
Joined
Aug 21, 2013
Messages
67
VBA Code:
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

Board Regular
Joined
Aug 21, 2013
Messages
67
I think ive fixed it with this formula on sheet 2 before it pastes the data on the 3rd.

VBA Code:
=IFERROR(DATEVALUE(N1),"")
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
707
Office Version
2013
Platform
Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,632
Office Version
365, 2010
Platform
Windows, Mobile
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,063
Messages
5,466,369
Members
406,478
Latest member
Amar kumar

This Week's Hot Topics

Top