Copy a selected range to another worksheet

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

Answers


There is mane 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.


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

Need Your Help

Have a different language for the admin interface than the site's default in Drupal

drupal internationalization

Is there a way for the admin interface to always show up in English, when the site's default language is e.g. Spanish?

Git how to overwrite directory with updated files

git tortoisegit

In my project I have subdirectory 'generated-src' that contain a lot of source files generated by other app. Then at some point I need to update this directory with the new version.