Please help, file name issue

  • Thread starter Thread starter brad
  • Start date Start date
B

brad

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
 
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
 
Back
Top