copying a range to first empty row is copying over data

**jseufert** · 08-12-2009, 04:01 PM

I have a form, that is to be used repeatedly. The info is parsed to a temp sheet in the same workbook where it is organized correctly and then copied to a different workbook, a batch file. I have two problems. 1, it's duplicating the first three rows of data from my source file when pasting to my destination file. 2, It's overwriting every previous row of data in my destination file instead of finding the first empty row and concatenating the data.

Heres my code.

Sub test3()
Dim x As Long, y As Long
Workbooks.Open Filename:="M:\aaclosing\2008\test\Funding Entries.xls"
x = Workbooks("Recon Builder.xls").Sheets("JEs").Range("a65536").End(xlUp).Row
y = Workbooks("Funding Entries.xls").Sheets("Funding Entries").Range("a65536").End(xlUp).Row
Workbooks("Recon Builder.xls").Sheets("JEs").Range("A2:G" & x).copy
Workbooks("Funding Entries.xls").Worksheets("Funding Entries").Range("A1:G" & y + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
end sub

**shg** · 08-12-2009, 04:16 PM

Maybe like this (untested):

Sub test3()
    Dim x       As Long
    Dim y       As Long
    Dim wksFr   As Worksheet
    Dim wksTo   As Worksheet

    Set wksFr = Workbooks("Recon Builder.xls").Worksheets("JEs")

    Workbooks.Open Filename:="M:\aaclosing\2008\test\Funding Entries.xls"
    Set wksTo = ActiveWorkbook.Worksheets("Funding Entries")

    x = wksFr.Cells(Rows.Count, "A").End(xlUp).Row
    y = wksTo.Cells(Rows.Count, "A").End(xlUp).Row + 1

    wksFr.Range("A2:G" & x).Copy
    wksTo.Cells(y, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                     SkipBlanks:=False, Transpose:=False
End Sub

**jseufert** · 08-12-2009, 05:20 PM

Thank you very much, works like a charm. Sir, I owe you a great debt of gratitude.

**royUK** · 08-13-2009, 03:45 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save