0

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.

braX
  • 11,506
  • 5
  • 20
  • 33
Fox
  • 33
  • 1
  • 9

1 Answers1

2

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
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Thanks for all these helpful suggestions BigBen. The reason I tried using the loop is because this copying of data over will happen more than once. So rather than new data to replace existing data, it should just add itself to the data that's already there. Once home, I am going to try implementing your code to see whether the problem is finally solved. – Fox Feb 13 '20 at 18:49
  • From what I can see of your code, looping is not needed to append to already existing data. – BigBen Feb 13 '20 at 18:50
  • Okay, so on testing it copied over the correct ranges without overlapping my helper columns:- Thanks. Only problem now is it is copying the entire column rows as well, including those without data. I need for it to copy and past up to the last used row that had data in it. ~ Thanks – Fox Feb 13 '20 at 19:09
  • Did you keep the lines that determined `LastRow`? – BigBen Feb 13 '20 at 19:19
  • Hey BigBen, so it seems to me that the problem is that it is copying the entire source table rows which are 1004 rows onto the destination sheet. I ran formulas down the entire table. These are ISBlank type formulas that I arranged to execute if there is data in particular cells - just to automate the process. So it seems like when copying and pasting, the program includes all these cells with formulas. Any idea on how to solve this? – Fox Feb 14 '20 at 16:18
  • 1
    If you have a table, perhaps see [this](https://stackoverflow.com/questions/43631926/lastrow-and-excel-table). – BigBen Feb 14 '20 at 16:20