Jon,

You need to open the workbook and as you correctly say use workbooks.open
but you also need the following:

dim wb_current as workbook, wb_archive as workbook

set wb_current = activeworkbook ' this records the current workbook
workbooks.open "c:\my documents\Archivefile.xls"
set wb_archive = activeworkbook ' this records the new work book

set rng = wb_archive.worksheets("Archive") _
.Cells(Rows.Count, 1).End(xlUp)(2).Offset(3, 0)

wb_current.worksheets("Form").Range("a18:h42").Cop y
rng.PasteSpecial xlFormats
rng.PasteSpecial xlValues

wb_archive.close savechanges:=True

set rng = nothing
set wb_archive = nothing
set wb_current = nothing ' if not needed again.


HTH



"Jon" wrote:

I am trying to archive some data via a macro. I want to copy data into
another workbook, not the one the macro is running in. Here is some
code I tried:

Sub test()

Dim rng As Range

Set rng = Sheets("Archive") _
.Cells(Rows.Count, 1).End(xlUp)(2).Offset(3, 0)

Sheets("Form").Range("a18:h42").Copy
rng.PasteSpecial xlFormats
rng.PasteSpecial xlValues

End Sub

This code does what I want for the most part, but pastes into the same
file. I want to open a different file called "archivefile.xls" and
paste into the "Archive" sheet of that file. It is important that I
have the .Cells(Rows.Count, 1).End(xlUp)(2).Offset(3, 0) information
there so that my data is not overwritten, Excel just finds the next
empty cells down to paste. The data I am copying is updated every few
minutes by an outside source, I need a way to archive that data because
it is overwritten once new data comes in. I know there will be some
commands similar to Workbooks.Open("Archivefile.xls") but I just havent
been able to get the syntax right to get it to work. Thanks in advance
for any help you can give.

- Jon