Please help, file name issue
 

How do I setup a macro to where no matter what file runs
the macro, it will open up a file named "email quote".
If I create the macro in book1 and then save the file to a
different name, the macro is always looking for "book1",
(subscript out of range, error)
instead of what ever the current file name is. Thanks for
any help.

Here is the code

Workbooks.Open Filename:= _
"C:\Documents and Settings\user\Desktop\Email
quote.xls"
Windows("Book1").Activate
Sheets("Sheet1").Select
Range("A1:A6").Select
Selection.Copy
Windows("Email quote.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A8").Select
Windows("Book1").Activate
End Sub

Please help, file name issue
 

Brad,

Try something like

Dim AWB As Workbook
Set AWB = ActiveWorkbook
Workbooks.Open Filename:= _
"C:\Documents and Settings\user\Desktop\Email quote.xls"
AWB.Activate
Sheets("Sheet1").Select
Range("A1:A6").Select
Selection.Copy
Windows("Email quote.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A8").Select
AWB.Activate
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"brad" wrote in message
...


How do I setup a macro to where no matter what file runs
the macro, it will open up a file named "email quote".
If I create the macro in book1 and then save the file to a
different name, the macro is always looking for "book1",
(subscript out of range, error)
instead of what ever the current file name is. Thanks for
any help.

Here is the code

Workbooks.Open Filename:= _
"C:\Documents and Settings\user\Desktop\Email
quote.xls"
Windows("Book1").Activate
Sheets("Sheet1").Select
Range("A1:A6").Select
Selection.Copy
Windows("Email quote.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A8").Select
Windows("Book1").Activate
End Sub

Please help, file name issue
 

As my son would say, "Genius!" Thanks Chip, that is
exactly what I am looking for.

-----Original Message-----
Brad,

Try something like

Dim AWB As Workbook
Set AWB = ActiveWorkbook
Workbooks.Open Filename:= _
"C:\Documents and Settings\user\Desktop\Email
quote.xls"
AWB.Activate
Sheets("Sheet1").Select
Range("A1:A6").Select
Selection.Copy
Windows("Email quote.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A8").Select
AWB.Activate
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"brad" wrote in
message
...


How do I setup a macro to where no matter what file runs
the macro, it will open up a file named "email quote".
If I create the macro in book1 and then save the file
to a
different name, the macro is always looking for "book1",
(subscript out of range, error)
instead of what ever the current file name is. Thanks
for
any help.

Here is the code

Workbooks.Open Filename:= _
"C:\Documents and Settings\user\Desktop\Email
quote.xls"
Windows("Book1").Activate
Sheets("Sheet1").Select
Range("A1:A6").Select
Selection.Copy
Windows("Email quote.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A8").Select
Windows("Book1").Activate
End Sub






.