PasteSpecial macro

  • Thread starter Thread starter CG Rosén
  • Start date Start date
C

CG Rosén

Good evening,

Need some help to clean up the recorded macro below. Sheet1 is the
ActiveSheet.
Trying to paste the values of Range("A1:A10") to Sheet2. Cant find out the
right syntax.
Thankful for any hints.

Brgds

CG Rosén
---------------------------------------------------------------------
Sheets("Sheet1").Select
Range("A1:A10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
_
:=False, Transpose:=False
 
Sheets("Sheet1").Range("A1:A10").Copy
Sheets("Sheet2").Range("A1").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Doesn't make any difference which sheet is the activesheet.

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

is another way to do it.
 
Maybe:

Option Explicit

Sub testme02()

Dim rng As Range
Set rng = Worksheets("sheet1").Range("a1:a10")

rng.Copy
Worksheets("sheet2").Range("a1").PasteSpecial Paste:=xlPasteValues

End Sub

but you could just assign the value, too:

Sub testme02()

Dim rng As Range
Set rng = Worksheets("sheet1").Range("a1:a10")

Worksheets("sheet2").Range("A1") _
.Resize(rng.Rows.Count, rng.Columns.Count).Value _
= rng.Value

End Sub
 
CG

You do not have to copy the data, this will perform the
same action

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

End Sub

Regards
Peter
 
Back
Top