Transfer Value from one sheet to another, not the formula

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
72
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?

VBA Code:
    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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
This will paste just the values. If you need formatting too then add a plus sign after xlpastevalies and choose the formats
VBA Code:
With wsCopy
    .Range(.Cells(1, 1), .Cells(lCopyLastRow, lastCol)).Copy
    wsDest.Range("A" & lDestLastRow).PasteSpecial xlPasteValues
End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,029
Office Version
  1. 365
Platform
  1. Windows
Another option if you want values only
VBA Code:
With wsCopy
    wsDest.Range("A" & lDestLastRow).Resize(lCopyLastRow, lastCol).Value .Range(.Cells(1, 1), .Cells(lCopyLastRow, lastCol)).Value
End With
 
Solution

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
72
Thanks All!!

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,029
Office Version
  1. 365
Platform
  1. Windows
Because copy destination is a direct copy that bypasses the clipboard & will copy everything values, formula formatting etc & cannot be used with pastespecial.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,756
Messages
5,597,933
Members
414,193
Latest member
bb60

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
Top