Excel VBA - Second parameter gets ignored when copying data using range-CodePudding

I am trying to copy part of a range of data from Sheet "Source" to sheet "Target" when clicking a button. The real code is more complex this is a simple example to illustrate the question.

My test data has 6 rows and 2 columns and I am trying to copy 3 rows and 2 columns.

When I am trying to copy the first 3 rows, it always copies the complete column:

Sub ButtonCopySourceToTarget_Clicked()

  Set vbaPractice= ThisWorkbook
  Set mySource = vbaPractice.Worksheets("Source")
  Set myTarget = vbaPractice.Sheets("Target")

  ' The second parameter of the Range function (&3) gets ignored - why?
  mySource.Range("A1:B1" & 3).Copy
  myTarget.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

CodePudding user response：

Copy Values More Efficiently

Option Explicit

Sub ButtonCopySourceToTarget_Clicked()

    ' Reference the workbook and the worksheets.
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim sws As Worksheet: Set sws = wb.Worksheets("Source")
    Dim tws As Worksheet: Set tws = wb.Worksheets("Target")
    
    ' Reference the source range ('srg').
    Dim srg As Range: Set srg = sws.Range("A1:B7")
    ' Reference the first three rows of the source range,
    ' the source copy range ('scrg').
    Dim scrg As Range: Set scrg = srg.Resize(3)
    
    ' Reference the first target cell ('tfCell').
    Dim tfCell As Range: Set tfCell = tws.Range("A1")
    ' Reference the target range ('trg'), a range of the same size as
    ' the source copy range.
    Dim trg As Range
    Set trg = tfCell.Resize(scrg.Rows.Count, scrg.Columns.Count)
    
    ' Copy values by assignment (most efficient).
    trg.Value = scrg.Value

End Sub