HELP!

  • Thread starter Thread starter needhelp
  • Start date Start date
N

needhelp

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",
instead of what ever the current file name is. Help!
Thanks.
 
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
 
Hi
the first line of your code is a hardcoded reference to this file:
Workbooks.Open Filename:= "C:\Documents and Settings\user\Desktop\Email
quote.xls"
Same is true for the line
Windows("Email quote.xls").Activate

what are you trying to achieve this this macro. Currently it copies a
range (A1:A6) from book1 to your email quote.xls (at position A1) and
re-activates book1 after finishing the copy.

Frank
 
Frank, the macro is really more complicated than what I am
showing. I just want to be able to use a file (call it
book1), run a macro. Then, update information on book1,
save it to a new file name (say book2), then run the macro
again. But the macro calls for book1 instead of book2. Is
there a way to where the macro will run regardless of what
the original file name is?
 
Hi
some ideas:
1. use INPUTBOX to ask for the specific file and use this name for
opening, activationg, etc.
2. use Getopenfilename (from the help file):
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
MsgBox "Open " & fileToOpen
End If
and use this variable as reference

3. You may also use Activeworkbook to reference the current workbook
(have a look at the Excel help for more details) -> Probably the best
way for your macro. e.g.
Sub foo()
....
with Activeworkbook.Sheets("Sheet1")
Range("A1:A6").copy
Range("B1:B6).paste
'.... more code
end with
end sub

Frank
 
Back
Top