ThisWorkbook refers to the workbook with the code, so if the code is in N
form.xls, then replace it with ThisWorkbook.

--
Regards,
Tom Ogilvy


"Jon" wrote in message
ups.com...
I have a macro that runs automatically. The computer is in an
environement where I don't want users to be able to modify the
spreadsheet or the code so I have the sheets and workbook protected.
The only workaround I can see that would hinder my VBA code is if the
user changed the actual filename of my spreadsheet (N form.xls). My
code is below...

Sub Archive()

Dim rng1 As Range
Dim rng2 As Range

Workbooks.Open "C:\Documents and
Settings\username\Desktop\Archivefile.xls"

Workbooks("Archivefile").Sheets("Archive").Unprote ct
Password:="password"

Set rng1 = Workbooks("Archivefile").Sheets("Archive") _
.Cells(Rows.count, 1).End(xlUp)(2).Offset(3, 0)

Workbooks("N Form").Activate

Sheets("Form").Select
Range("A3:B5").Copy
rng1.PasteSpecial xlFormats
rng1.PasteSpecial xlValues

Set rng2 = Workbooks("Archivefile").Sheets("Archive") _
.Cells(Rows.count, 1).End(xlUp)(2).Offset(1, 0)

Workbooks("N Form").Activate

Sheets("Form").Select
Range("A18:B42,F18:F42,G18:H42").Copy
'rng2.PasteSpecial xlFormats
rng2.PasteSpecial xlValues

Workbooks("Archivefile").Sheets("Archive").Protect
Password:="password"

Application.CutCopyMode = False
Workbooks("Archivefile").Save
Workbooks("Archivefile").Close

'Workbooks("N Form").Sheets("Form").PrintOut Copies:=1

End Sub


You can see I use Workbooks("N Form").Activate to bring up the
workbook that contains my 'Archive' macro. My question is, can I
activate workbooks without referring to their filename? If this isn't
possible, I suppose some sort of protection against renaming the file
in Windows is in order...