Transfer Value from one sheet to another, not the formula

[DanSMT]

All,

I see a lot of people copy and pasting data from other sheets using copy and paste, however it doesn't appear to work when using a destination. Any ideas how to fix this?

    For Each WSheet In Worksheets
    
    With WSheet
        
        lastRow = .Range("A" & Rows.Count).End(xlUp).row
      
            If .Range("J" & lastRow).Value = "<>" Then
            
            ElseIf .Range("A" & lastRow).Value < Date Then
    
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lastCol As Long
Dim lDestLastRow As Long

  Set wsCopy = WSheet
  Set wsDest = Workbooks("due.xls").Worksheets("sheet1")

    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, 1).End(xlUp).row

    lastCol = wsCopy.Cells(2, wsCopy.Columns.Count).End(xlToLeft).Column

    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).row

With wsCopy
    .Range(.Cells(1, 1), .Cells(lCopyLastRow, lastCol)).Copy wsDest.Range("A" & lDestLastRow)
End With
            End If

 

[gallen]

This will paste just the values. If you need formatting too then add a plus sign after xlpastevalies and choose the formats

With wsCopy
    .Range(.Cells(1, 1), .Cells(lCopyLastRow, lastCol)).Copy
    wsDest.Range("A" & lDestLastRow).PasteSpecial xlPasteValues
End With

 

[Fluff]

Another option if you want values only

With wsCopy
    wsDest.Range("A" & lDestLastRow).Resize(lCopyLastRow, lastCol).Value .Range(.Cells(1, 1), .Cells(lCopyLastRow, lastCol)).Value
End With

 

[DanSMT]

Thanks All!!

Why wouldnt the script work when attempting to add .pastespecial xlpastevalues to the script without breaking the line apart?

 

[Fluff]

Because copy destination is a direct copy that bypasses the clipboard & will copy everything values, formula formatting etc & cannot be used with pastespecial.