5

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
pablo.vix
  • 2,103
  • 2
  • 15
  • 12

8 Answers8

6

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
MP24
  • 3,110
  • 21
  • 23
  • I tried this but it show me other problem. I was using values like ''value 1'. Using this technique when the value was copied it became 'value 1', disapearing the first "'" character. Not what I want, it should be exactly the same value. – pablo.vix Jun 20 '14 at 15:19
5

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
pbou
  • 298
  • 3
  • 13
4

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
Kory
  • 308
  • 1
  • 7
  • Glad I could help! If I provided the best answer for you, selecting my answer by clicking the check mark would be of great benefit to me! I am just starting out on this site and getting the initial reputation is important! Thanks! – Kory Jun 20 '14 at 17:18
1

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
gtwebb
  • 2,981
  • 3
  • 13
  • 22
1

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.

Saurabh Bhandari
  • 2,438
  • 4
  • 26
  • 33
RHH1095
  • 89
  • 1
  • 4
0
r.Copy
dest.pastespecial xlPastevalues
user2649602
  • 11
  • 1
    Instead of just posting a code fragment, you should expand your answer to explain what you're doing and why it answers the OP's question. – azurefrog Jun 18 '14 at 21:37
0

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
pablo.vix
  • 2,103
  • 2
  • 15
  • 12
0

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

Jawali M
  • 3
  • 3