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

Fixed!

I just had to do

Workbooks.Open "Archivefile.xls"

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

Works just like I wanted it to.

Here is some code that copies a range from one worksheet to another worksheet

Sub Test()
Dim wbkSource As Workbook
Dim wbkDestination As Workbook
Dim wksSource As Worksheet
Dim wksDestination As Worksheet
Dim rngSource As Range
Dim rngDestination As Range

'Set your source
Set wbkSource = ThisWorkbook
Set wksSource = wbkSource.Sheets("Sheet1")
Set rngSource = wksSource.Cells

'Set your destination
On Error GoTo OpenBook
Set wbkDestination = Workbooks("ThatBook.xls")
On Error GoTo 0
Set wksDestination = wbkDestination.Sheets("Sheet1")
Set rngDestination = wksDestination.Cells(rows.count,
"A").end(xlUp).offset(1, 0)

'You now have all of your souce and destination objects

rngSource.Copy rngDestination

Exit Sub

OpenBook:
Set wbkDestination = Workbooks.Open("C:\Thatbook.xls")
Resume Next
Exit Sub

End Sub

--
HTH...

Jim Thomlinson


"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

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