Copy/Paste Date format into a column on a WS in another workbook

Question

I am using data from two different sources. Each source has formatted their date/time differently. I need to put both sets of data into one worksheet and remove duplicates. The date format differences is preventing this. I am trying to copy the date format from workbook "A" to a column range of existing data in workbook "B" so that when I copy the data over from workbook "A" to the end of workbook "B", the date formats will match.

The date format in workbook "A" is:

The Date format in workbook "B" is:

I have supplied the entire code below. But here is the line I have added:

sourceWorksheet.Range("G2").Copy destinationWorksheet.Range("G2:H2000").PasteSpecial(xlPasteFormats, Operation:=xlNone, SkipBlanks:=False)

Which gives me the following error:

Run-time error '1004': Unable to get the PasteSpecial property of the Range class

Here is the entire code set:

Sub QA_1603_March()
'
    Dim ANS As Long
    Dim LR As Long
    Dim uRng As Range
    Dim she As Worksheet

    ANS = MsgBox("Is the March 2016 Swivel Master File checked out of SharePoint and currently open on this desktop?", vbYesNo + vbQuestion + vbDefaultButton1, "Master File Open")
    If ANS = vbNo Or IsWBOpen("Swivel - Master - March 2016") = False Then
        MsgBox "The required workbook is not currently open. This procedure will now terminate.", vbOKOnly + vbExclamation, "Terminate Procedure"
        Exit Sub
    End If

    Call Verification_Format_WS

    Dim sourceWorkBook As Workbook
        Set sourceWorkBook = Workbooks("Verification Temp.xlsx")
    Dim destinationWorkbook As Workbook
        Set destinationWorkbook = Workbooks("Swivel - Master - March 2016.xlsm")
    Dim sourceWorksheet As Worksheet
        Set sourceWorksheet = sourceWorkBook.Sheets("Verification")
    Dim destinationWorksheet As Worksheet
        Set destinationWorksheet = destinationWorkbook.Sheets("Validation")

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    sourceWorksheet.Cells.EntireRow.Hidden = False

    sourceWorksheet.Range("G2").Copy destinationWorksheet.Range("G2:H2000").PasteSpecial(xlPasteFormats, Operation:=xlNone, SkipBlanks:=False)

    For LR = sourceWorksheet.Range("J" & Rows.Count).End(xlUp).row To 2 Step -1
        If sourceWorksheet.Range("J" & LR).Value <> "3" Then
            If uRng Is Nothing Then
                Set uRng = sourceWorksheet.Rows(LR)
            Else
                Set uRng = Union(uRng, sourceWorksheet.Rows(LR))
            End If
        End If
    Next LR

    If Not uRng Is Nothing Then uRng.Delete
        For Each she In destinationWorkbook.Worksheets
        If she.FilterMode Then she.ShowAllData
    Next

    With sourceWorksheet.Sort
        With .SortFields
            .Clear
            .Add Key:=Range("A2:A2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("G2:G2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("B2:B2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("C2:C2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("D2:D2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("E2:E2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange Range("A2:AE2000")
        .Apply
    End With

    sourceWorksheet.Cells.WrapText = False

    Dim lastRow As Long
    lastRow = sourceWorksheet.Range("A" & Rows.Count).End(xlUp).row
    Dim destinationRow As Long
    destinationRow = destinationWorksheet.Cells(Rows.Count, 1).End(xlUp).row + 1
    sourceWorksheet.Range("A2:J" & lastRow).Copy destinationWorksheet.Range("A" & destinationRow)

    Call Verification_Save
    Call Verification_Delete_Temp_Workbook

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub

I have tried a few variations of this from examples I have found here, but I keep getting errors of one kind or another.

Thank you both for your assistance. Well, this did not resolve my issue. Both answers below are good ones, but I chose to go with Kevin's. However, I thought that since the column had dates that were formatted differently that when I ran the code for removing duplicates, that was causing the duplicates not to be removed from the worksheet. As it turns out, this was not the issue. So my hunt goes on to resolve the duplicate issue. I smell a new question coming up... — Iron Man

Kevin Kevin · Accepted Answer · 2016-03-09T19:02:16

As @Scott said, the copy line needs to be split into two lines. However, you do not need the Operation:=xlNone, SkipBlanks:=False parts because by default they will be set to that. The following should work.

sourceWorksheet.Range("G2").Copy
destinationWorksheet.Range("G2:H2000").PasteSpecial xlPasteFormats

*Note: You don't need the parenthesis to pass the parameter in this case.

Copy/Paste Date format into a column on a WS in another workbook

2 Answers