CODEWITHSUNDEEP
excelexcel-2010vba
Taylor Zwick
Taylor Zwick

Reputation: 27

Excel copy paste value different worksheets

In sheet1 I want to copy the values of the columns a,c,d,g and paste that to the bottom of sheet2 in columns a,b,c,d

I've been having difficultly with this because the columns of a,c,d,g contain formulas so when I use copy/destination method it ends up breaking the macro.

Upvotes: 0

Views: 2149

Answers (3)

SeanC
SeanC

Reputation: 15923

I can think of 3 methods, and all essentially use copy/paste

  1. Use the method that rwtwm suggested. Example code would be

    Sheets("Sheet1").Range("A1:A10").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

  2. copy the values

    Sheets("Sheet2").Range("A1:A10").Value = _
    Sheets("Sheet1").Range("A1:A10").Value

  3. Copy the data into an array, and then use that to copy that array into the other sheet

    Dim MyData
MyData = Sheets("Sheet1").Range("A1:A10")
Sheets("Sheet2").Range("A1:A10") = MyData

Upvotes: 0

Derek Cheng
Derek Cheng

Reputation: 525

Personally I would use copy and pastespecial because then you'll have the choice of just pasting the values or formatting as well. But if you don't like that route you can also assign the values in a column to an array and paste it in.

Here's an example of how you would go about such problem:

Sub test()
Dim colA As Variant
Dim colB As Variant
Dim colC As Variant
Dim lrow As Long

'putting the ranges into arrays
With Sheets(1).UsedRange
    colA = .Columns("A")
    colB = .Columns("B")
    colC = .Columns("C")
End With

'putting arrays back into ranges
'note the use of resize to fit the target range with arrays
With Sheets(2)
    lrow = .UsedRange.Rows.Count + 1
    .Range("A" & lrow).Resize(UBound(colA, 1), 1) = colA
    .Range("B" & lrow).Resize(UBound(colB, 1), 1) = colB
    .Range("C" & lrow).Resize(UBound(colC, 1), 1) = colC
End With

End Sub

Upvotes: 1

rwtwm
rwtwm

Reputation: 23

Use the paste special method on the range in question. See the link at the Microsoft help resource. You will require the modifier xlPasteValues.

Upvotes: 0

Related Questions