Paste Only values and number formatting using last row

Question

I have the below code that works fine. However, the location where the range is copying the cells is formatted as a table. The table has banded rows with data validations in each cell. When I run the code it pastes the values as expected below the next available empty row but it also brings along the cells table formatting and data validation.

I would like to copy and paste over ONLY the cells value and number formatting using the "last row" VBA method.

 If MsgBox("You selected Archive. Do you wish to continue?", vbYesNo) = vbYes Then
        MsgBox "Archiving to Completed Appointments Log..."
     
     Else
     If MsgBox("You selected Archive. Do you wish to continue?", vbYesNo) = vbNo Then MsgBox "Archiving Cancelled!"
     
     Exit Sub
        
    End If
    Application.ScreenUpdating = False
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  'Set variables for copy and destination sheets
  Set wsCopy = Workbooks("Tag Up - Service Desk - Appointments.xlsm").Worksheets("Previous Appointments")
  Set wsDest = Workbooks("Tag Up - Service Desk - Appointments.xlsm").Worksheets("Completed Appointments Log")
    
  '1. Find last used row in the copy range based on data in column A
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    
  '2. Find first blank row in the destination range based on data in column A
  'Offset property moves down 1 row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

  '3. Copy & Paste Data
  wsCopy.Range("A4:D" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)
End Sub

BigBen BigBen · Accepted Answer · 2020-10-28T17:50:58

Use Range.PasteSpecial:

wsCopy.Range("A4:D" & lCopyLastRow).Copy
wsDest.Range("A" & lDestLastRow).PasteSpecial xlPasteValuesAndNumberFormats

Paste Only values and number formatting using last row

1 Answers