Copia un intervallo selezionato in un altro foglio di lavoro

Sto usando il codice sotto il quale sto cercando di cambiare per non usare .select

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

Ho provato a utilizzare questo, ma non vi è alcun output per l’altro foglio di lavoro.

 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 

Ci sono modi mana per farlo, ma qui ne seguono due.

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 

Dimmi se hai bisogno di miglioramenti.

Ecco alcuni esempi su Come evitare l’utilizzo di Seleziona in Excel VBA Link StackOverflow

Ecco i semplici di

copia / incolla – valori = valori – metodo PasteSpecial

 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 

Semplice copia / incolla

 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 

Incolla il metodo speciale

 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