Setting a range when pasting data onto a sheet

Question

I am copying data between 2 Excel workbooks using vba:

Data source (copying from): Workbook x, 'waiting list' sheet.
Data destination (pasting to): Workbook y, 'ALL Years data' sheet.

It's become a problem because in workbook y on the destination sheet I am pasting data to, I added helper columns to assist my data filtering. However, now when I copy and paste data (automatically with vba) from the source sheet to the destination sheet, it seems to copy over all columns (with or without data) which then overlap my helper columns, leaving the whole area blank.

I would like to now limit the range onto where data is pasted i.e. from column A to column W, instead of pasting over my helper columns X,Y and Z and the entire remaining columns.

Please see my code below:-

Sub CopyToYearly2()

 Dim LastRow As Long
 Dim i As Long, j As Long
 Dim answer As Integer

answer = MsgBox("Do you intend to copy this data to the Yearly analysis sheet?", vbYesNo + vbQuestion, "Copy data to Yearly analysis sheet.")


   Set x = ThisWorkbook

   If answer = vbYes Then

   Set y = Workbooks.Open("S:\Downloads\workbook y.xlsm")


   With x.ActiveSheet 'Determine last used row on waitinglist.
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With

   With y.Sheets("ALL Years Data")
      j = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
   End With

   For i = 3 To LastRow 

       'With x.Sheets("Waiting List")
       With x.ActiveSheet 
               .Rows(i).Copy Destination:=y.Sheets("ALL Years Data").Range("A" & j)
               j = j + 1

       End With
   Next i

   'Save x and y:
x.Save
y.Save

 Else
    'do nothing

End If


End Sub

Thank You.

The problem is .Rows(i).Copy... you want to restrict that to certain columns instead of an entire row. — BigBen
But you don't need a loop here to copy. Just do it in one step: — BigBen

BigBen BigBen · Accepted Answer · 2020-02-13T18:15:13

You don't need a loop to copy. Do it in one step. Also, don't use Rows, but restrict the range to be copied to the columns you want:

x.Worksheets("Waiting List").Range("A3:W" & LastRow).Copy _
     Destination:=y.Worksheets("ALL Years Data").Range("A" & j)

Also

Dim answer As Integer

is probably better as

Dim answer as VbMsgBoxResult

Setting a range when pasting data onto a sheet

1 Answers