Paste Values instead of Paste

[gheyman]

This code is pasting formatting - is there a way to change this so that it doesnt?

Sheet9.ListObjects("MaterialCostSource").DataBodyRange.Copy Sheet13.Range("A2")

Sub CopyCostSourceTable()
'*4 After a selection is made, copy the complete Cost Source list to Viewed Cost Sources. This enables the user  to see what was already evlauted and what is new

Dim lRow As Long
Dim lRow2 As Long

'*Sheet 9 = Cost SOurce
'*Sheet13 = Viewed Cost Sources

'*Find Last row in destination table
lRow = Sheet13.Cells(Rows.Count, "A").End(xlUp).Row
  
'* Copy Source table and Paste to bottom of Destination table

    If Sheet13.Range("A2").Value = "" Then
         Sheet9.ListObjects("MaterialCostSource").DataBodyRange.Copy Sheet13.Range("A2")
    Else
        Sheet9.ListObjects("MaterialCostSource").DataBodyRange.Copy Sheet13.Range("A" & lRow + 1)
    End If
  
'*Find Last row after data is copied in
lRow2 = Sheet13.Cells(Rows.Count, "AM").End(xlUp).Row
'*Add Date and Time to new added data
Sheet13.Range("AM" & lRow & ":" & "AM" & lRow2).Value = Now()

End Sub

Thanks

 

[Rick Rothstein]

Try changing the line of code at the beginning of your message to these two lines...

Sheet9.ListObjects("MaterialCostSource").DataBodyRange.Copy
Sheet13.Range("A2").PasteSpecial xlPasteValues

 

[Max1616]

Hello,

This will paste whatever is in the cell and drop the formats

Sub CopyCostSourceTable()
'*4 After a selection is made, copy the complete Cost Source list to Viewed Cost Sources. This enables the user  to see what was already evlauted and what is new

Dim lRow As Long
Dim lRow2 As Long

'*Sheet 9 = Cost SOurce
'*Sheet13 = Viewed Cost Sources

'*Find Last row in destination table
lRow = Sheet13.Cells(Rows.Count, "A").End(xlUp).Row
  
'* Copy Source table and Paste to bottom of Destination table

    If Sheet13.Range("A2").Value = "" Then
         Sheet9.ListObjects("MaterialCostSource").DataBodyRange.Copy
         Sheet13.Range("A2").PasteSpecial xlPasteFormulas
    Else
        Sheet9.ListObjects("MaterialCostSource").DataBodyRange.Copy
        Sheet13.Range("A" & lRow + 1).PasteSpecial xlPasteFormulas
    End If
  
'*Find Last row after data is copied in
lRow2 = Sheet13.Cells(Rows.Count, "AM").End(xlUp).Row
'*Add Date and Time to new added data
Sheet13.Range("AM" & lRow & ":" & "AM" & lRow2).Value = Now()

End Sub

: