Final Row help

[allison]

definately a vba newbie, but I'm trying hard. I've got a spreadsheet that needs to have particular data points written out to a log. I've worked on a procedure that finds the log, opens it and writes to it - but I don't know how to get it to append records to the log instead of writing over the existing information. If I use what is written below, I get an object error on
Set FinalRow line. I obviously am doing something really wrong. Any suggestions?

[VBA]
Sub CopyData()
Dim DestBook As Workbook, SrcBook As Workbook

Application.ScreenUpdating = False
Set SrcBook = ThisWorkbook

SrcBook.Activate
Set FinalRow = Range("A65536").End(xlUp).Row
On Error Resume Next
Set DestBook = Workbooks.Open("e:\copy\Book1.xls")
If Err.Number = 1004 Then
Set DestBook = Workbooks.Add
SrcBook.Worksheets(2).Range("A1:F13").Copy
DestBook.Worksheets(1).Range("A" & FinalRow).PasteSpecial
Application.CutCopyMode = False
DestBook.SaveAs ("e:\copy\Book1.xls")
DestBook.Close
Else
SrcBook.Worksheets(2).Range("A1:F13").Copy
DestBook.Worksheets(1).Range("A" & FinalRow).PasteSpecial
Application.CutCopyMode = False
DestBook.Save
DestBook.Close
End If


On Error GoTo 0
Set DestBook = Nothing
Set SrcBook = Nothing
End Sub
[/VBA]

[Bob Phillips]

Not tested, but try this

[vba]

Sub CopyData()
Dim DestBook As Workbook, SrcBook As Workbook
Dim FinalRow As Long

Application.ScreenUpdating = False
Set SrcBook = ThisWorkbook

On Error Resume Next
Set DestBook = Workbooks.Open("e:\copy\Book1.xls")
If Err.Number = 1004 Then

Set DestBook = Workbooks.Add
End If

With DestBook.Worksheets(1)

FinalRow = .Range("A" & .Rows.Count).End(xlUp).Row
SrcBook.Worksheets(2).Range("A1:F13").Copy DestBook.Worksheets(1).Range("A" & FinalRow + 1)
DestBook.Save
DestBook.Close
End With

On Error GoTo 0
Set DestBook = Nothing
Set SrcBook = Nothing
End Sub
[/vba]

[allison]

this works great! Thanks so much for your help!!!