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
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