Copy a selected range to another worksheet

Question

I am using code below which I am trying to change so as not to use .select

Selection.Select ' from active worksheet
    Selection.Copy
    Sheets("Purch Req").Select
    Range("A1").Select
    ActiveSheet.Paste

I have tried using this but there is no output to the other worksheet.

Dim src2Range As Range, dest2Range As Range

    Set src2Range = Selection 'source from selected range

    Set dest2Range = Sheets("Purch Req").Range("A1").Resize(src2Range.Rows.Count, src2Range.Columns.Count) ' destination range _
    'in Purch req worksheet

Try `Selection.Copy Sheets("Purch Req").Range("A1")`. – Fadi Jan 19 '16 at 20:01 — Fadi, Jan 19 '16 at 20:01

score 7 · Answer 1 · edited May 23 '17 at 11:33

Here is good examples on How to avoid using Select in Excel VBA Link stackoverflow

Here is simples of

copy/paste - values = values - PasteSpecial method

Option Explicit
'// values between cell's
Sub PasteValues()

    Dim Rng1 As Range
    Dim Rng2 As Range

    Set Rng1 = Range("A1")
    Set Rng2 = Range("A2")
    Rng2.Value = Rng1.Value

    'or
    [A2].Value = [A1].Value

    'or
    Range("A2").Value = Range("A1").Value

    'or
    Set Rng1 = Range("A1:A3")
    Set Rng2 = Range("A1:A3")
    Rng2("B1:B3").Value = Rng1("A1:A3").Value

    'or
    [B1:B3].Value = [A1:A3].Value


    '// values between WorkSheets
    Dim xlWs1 As Worksheet
    Dim xlWs2 As Worksheet

    Set xlWs1 = Worksheets("Sheet1")
    Set Rng1 = xlWs1.Range("A1")

    Set xlWs2 = Worksheets("Sheet2")
    Set Rng2 = xlWs2.Range("A1")
    Rng2.Value = Rng1.Value

    'or
    Set Rng1 = [=Sheet1!A1]
    Set Rng2 = [=Sheet2!A1]
    Rng2.Value = Rng1.Value

    'or
    [=Sheet2!A1].Value = [=Sheet1!A1].Value

    'or
    Worksheets("Sheet2").Range("A2").Value = Worksheets("Sheet1").Range("A1").Value

    '// values between workbooks
    Dim xlBk1 As Workbook
    Dim xlBk2 As Workbook

    Set xlBk1 = Workbooks("Book1.xlsm")
    Set Rng1 = xlBk1.Worksheets("Sheet1").Range("A1")

    Set xlBk2 = Workbooks("Book2.xlsm")
    Set Rng2 = xlBk2.Worksheets("Sheet1").Range("A1")
    Rng2.Value = Rng1.Value

    'or
    Set Rng1 = Evaluate("[Book1.xlsm]Sheet1!A1")
    Set Rng2 = Evaluate("[Book2.xlsm]Sheet2!A1")
    Rng2.Value = Rng1.Value

    'or
    Evaluate("[Book2.xlsm]Sheet2!A1").Value = Evaluate("[Book1.xlsm]Sheet1!A1")

    'or
    Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").Value = _
        Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value


End Sub

Simple copy/paste

Sub CopyRange()
    Dim Rng1 As Range
    Dim Rng2 As Range

    Set Rng1 = Range("A1")
    Set Rng2 = Range("A2")
    Rng1.Copy Rng2

    [A1].Copy [A2]

    Range("A2").Copy Range("A1")

    '// Range.Copy to other worksheets
    Dim xlWs1 As Worksheet
    Dim xlWs2 As Worksheet

    Set xlWs1 = Worksheets("Sheet1")
    Set Rng1 = xlWs1.Range("A1")
    Set xlWs2 = Worksheets("Sheet2")
    Set Rng2 = xlWs2.Range("A1")
    Rng1.Copy Rng2

    Set Rng1 = [=Sheet1!A1]
    Set Rng2 = [=Sheet2!A1]
    Rng1.Copy Rng2

    [=Sheet1!A1].Copy [=Sheet2!A1]

    Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A1")

    ''// Range.Copy to other workbooks
    Dim xlBk1 As Workbook
    Dim xlBk2 As Workbook

    Set xlBk1 = Workbooks("Book1.xlsm")
    Set Rng1 = xlBk1.Worksheets("Sheet1").Range("A1")
    Set xlBk2 = Workbooks("Book2.xlsm")
    Set Rng2 = xlBk2.Worksheets("Sheet2").Range("A2")
    Rng1.Copy Rng2


    Evaluate("[Book1.xlsm]Sheet1!A1").Copy Evaluate("[Book2.xlsm]Sheet2!A2")

    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy _
    Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")

End Sub

PasteSpecial method

Sub PasteSpecial()

    'Copy and PasteSpecial a Range
    Range("A1").Copy
    Range("A3").PasteSpecial Paste:=xlPasteFormats

    'Copy and PasteSpecial a between worksheets
    Worksheets("Sheet1").Range("A2").Copy
    Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteFormulas

    'Copy and PasteSpecial between workbooks
    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy
    Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteFormats

    Application.CutCopyMode = False

End Sub

Upvoted just for recommending to avoid `Select`. Implicit `ActiveSheet` and `ActiveWorkbook` references should be freakin' compile errors in VBA... note that `Sheets` is implicitly referring to the active workbook ;-) — Mathieu Guindon, Jan 19 '16 at 20:12
Big plus and an LOL on the *'should be freakin' compile errors in VBA'* although that might throw the macro recorder into conniptions. @Om3r, while direct value transfer is the 'best practise' in most cases, the OP's method was trying to bring across formatting and formulas. I generally only offer this (without explanation) if a [xlPasteValues](https://msdn.microsoft.com/en-us/library/office/ff837425.aspx) had been specified. — , Jan 19 '16 at 20:38

Elbert Villarreal · Accepted Answer · 2019-11-01T22:29:09.513

There is many ways to do that, but here goes two.

1)

Sub pasteExcel()
    Dim src2Range As Range
    Dim dest2Range As Range
    Dim r 'to store the last row
    Dim c 'to store the las column
    Set src2Range = Selection 'source from selected range

    r = Range("A1").End(xlDown).Row 'Get the last row from A1 to down
    c = Range("A1").End(xlToRight).Column 'Get the last Column from A1 to right
    Set dest2Range = Range(Cells(1, 1), Cells(r, c))
    dest2Range.PasteSpecial xlPasteAll
    Application.CutCopyMode = False 'Always use the sentence.
End Sub

2)

Sub pasteExcel2()
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet 'not used!
    Dim src2Range As Range
    Dim dest2Range As Range
    Dim r 'to store the last row
    Dim c 'to store the las column

    Set sht1 = Sheets("Sheet1")
    Set sht2 = Sheets("Sheet2")

    sht1.Activate 'Just in case... but not necesary

    r = Range("A1").End(xlDown).Row 'Get the last row from A1 to down
    c = Range("A1").End(xlToRight).Column 'Get the last Column from A1 to right
    Set src2Range = Range(Cells(1, 1), Cells(r, c)) 'source from selected range
    Set dest2Range = Range(Cells(1, 1), Cells(r, c))
    sht2.Range(dest2Range.Address).Value = src2Range.Value 'the same range in the other sheet. 
End Sub

Tell me if you need some improvement.

Thanks all for the response. I am totally awed at how quick you guru's are. I will be testing the solutions and reporting back. — HarryPea, Jan 20 '16 at 06:01
As the range of cells that I wanted to copy had been determined and selected already @fadi response worked without a hitch. Thank you everyone for being generous with your time. I really appreciate it. Not sure how to select a comment as an answer! — HarryPea, Jan 20 '16 at 18:00

Copy a selected range to another worksheet

2 Answers2

Linked