将选定范围复制到另一个工作表 [英] Copy a selected range to another worksheet

问题描述

我正在使用下面的代码，我试图对其进行更改以免使用 .select

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

推荐答案

有很多方法可以做到这一点，但这里有两种.

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.

这篇关于将选定范围复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！