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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,071
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,071
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,850
Messages
5,598,441
Members
414,240
Latest member
xnanx

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