CODEWITHSUNDEEP
excelvbacopyrange
pablo.vix
pablo.vix

Reputation: 2233

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

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

Upvotes: 7

Views: 99544

Answers (8)

Jawali M
Jawali M

Reputation: 3

You can skip the copy command altogether as MP24 said .. his suggestion worked for me after modifyibg the last line from "value" to "formula" as follows

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

dest.formula = r.formula

Upvotes: 0

pbou
pbou

Reputation: 395

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

Upvotes: 6

RHH1095
RHH1095

Reputation: 99

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.

Upvotes: 1

pablo.vix
pablo.vix

Reputation: 2233

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

Upvotes: 0

MP24
MP24

Reputation: 3200

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

Upvotes: 8

user2649602
user2649602

Reputation: 11

r.Copy
dest.pastespecial xlPastevalues

Upvotes: 0

gtwebb
gtwebb

Reputation: 3011

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

Upvotes: 1

Kory
Kory

Reputation: 318

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

Upvotes: 5

Related Questions