How to copy only values in excel vba from a range?

Question

I'm trying to copy values from a table to a Range, in Excel using vba Macros, but I dont want the table format, only its values. How can I achieve this?

Here is part of the code:

    'Source range
    Set r = Sheets("Sheet1").Range("Table1")

    'Destination range
    Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))

    r.Copy Destination:= dest

Answer 1

You can skip the copy command altogether by assigning the values of the source range to your destination range:

'Source range
Set r = Sheets("Sheet1").Range("Table1")
'Destination range
Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))

dest.Value = r.Value

Answer 2

I believe you are looking for the functionality of pasting values. You can record it, or use what I have done below. (from recording so selecting is in there, which will make it run slower, but you aren't looping so it is only constant time being added).

Selection.Copy
        'Select your destination like range("destination").select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

Answer 3

I assume you want to copy from "Sheet1" to "Sheet1" - of course you can make this a parameter and loop through all your sheets

Dim rSource as range 'Source Range
Dim rDest as range   'Target Range - It should be the same dimension
Dim wbSource as workbook 'Source Workbook
Dim wbTarget as workbook 'Target Workbook
Dim myRange as string

myRange = "A:G" ' It is an example, you can make it dynamic

'add new workbook
    Workbooks.Add
    Set wbTarget = ActiveWorkbook

'Set the Source Range
    Set rSource = wbSource.Sheets("Sheet1").Range(myRange)

'Destination Range
    Set rDest = wbTarget.Sheets("Sheet1").Range(myRange)

'Copy values only
    rSource.Copy
    rDest.PasteSpecial xlPasteValues

Answer 4

You need to use the pastespecial command as shown below.

'Source range
Set r = Sheets("Sheet1").Range("Table1")

'Destination range
Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))

r.Copy 
dest.pastespecial paste:=xlPasteValues

Answer 5

r.Copy
dest.pastespecial xlPastevalues

Answer 6

I achieve a solution that works.

There follows the code:

    Set r = Sheets("Criteria").Range("CriteriaTable")

    Set dest = Range(.Cells(linhaAtual, 1), .Cells(linhaAtual + r.Rows.Count - 1, 5))
    Sheets("Criteria").Activate
    r.Select
    Selection.Copy
    Sheets("Load").Activate
    dest.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Answer 7

Use the Range.Value method. Its like setting a variable a = 1. Where you think of it as copying 1 to a. So...

Range2.value = Range1.value

or

Worksheets("historical").Range("A1:F15").Value =  Worksheets("actuals").Range("A1:F15").Value

Here I'm copying some data in worksheet actual to some historical worksheet. Or if you prefer setting the value of one range to another range.