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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Your code should work. Are you sure your formula which evaluates to "16/02/2020" evaluates to a Date (ie 43877), instead of Text?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I think ive fixed it with this formula on sheet 2 before it pastes the data on the 3rd.

VBA Code:
=IFERROR(DATEVALUE(N1),"")
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,729
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top