I created the following module by doing a macro record. It works by running
it within the module but when I put in a project, I get a 1004 error on the
range select. The xls file is visable while I am stepping (F8). I even did
a call statement and get the same error and change the project from Private
to Public. What am I missing?

Workbooks.Open Filename:="C:\temp\jul 07.xls"
Worksheets(2).Activate
Range("B216:Y242").Select
Selection.Copy

John

Dim bk as Workbook, sh as Worksheet
set bk = Workbooks.Open( Filename:="C:\temp\jul 07.xls")
set sh = bk.Worksheets(2).Activate
sh.Range("B216:Y242").Select
Selection.Copy

also, you don't need to select or activate

Dim bk as Workbook
set bk = Workbooks.Open( Filename:="C:\temp\jul 07.xls")
bk.Worksheets(2).Range("B216:Y242").Copy

--
Regards,
Tom Ogilvy


"John" wrote:

I created the following module by doing a macro record. It works by running
it within the module but when I put in a project, I get a 1004 error on the
range select. The xls file is visable while I am stepping (F8). I even did
a call statement and get the same error and change the project from Private
to Public. What am I missing?

Workbooks.Open Filename:="C:\temp\jul 07.xls"
Worksheets(2).Activate
Range("B216:Y242").Select
Selection.Copy

John

Thank you. In ref select and activate, I was breaking it down to narrow down
where the error was.

John

"Tom Ogilvy" wrote in message
...
Dim bk as Workbook, sh as Worksheet
set bk = Workbooks.Open( Filename:="C:\temp\jul 07.xls")
set sh = bk.Worksheets(2).Activate
sh.Range("B216:Y242").Select
Selection.Copy

also, you don't need to select or activate

Dim bk as Workbook
set bk = Workbooks.Open( Filename:="C:\temp\jul 07.xls")
bk.Worksheets(2).Range("B216:Y242").Copy

--
Regards,
Tom Ogilvy


"John" wrote:

I created the following module by doing a macro record. It works by
running
it within the module but when I put in a project, I get a 1004 error on
the
range select. The xls file is visable while I am stepping (F8). I even
did
a call statement and get the same error and change the project from
Private
to Public. What am I missing?

Workbooks.Open Filename:="C:\temp\jul 07.xls"
Worksheets(2).Activate
Range("B216:Y242").Select
Selection.Copy

John