PDA

View Full Version : Final Row help



allison
02-11-2008, 12:29 PM
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?


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

Bob Phillips
02-11-2008, 03:18 PM
Not tested, but try this



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

allison
02-14-2008, 02:56 PM
this works great! Thanks so much for your help!!!