Macro Upon Opening Then Close

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am hoping to create a macro that upon opening a file it will do the
following:

1)Copy A1:B:2 (just as an example; also - there is only one worksheet in
this file)
2)Activate Workbook "Price Chart Template" (only 1 sheet here too)
3)Paste on A:1
4)Go back to the original document and close it without saving (this is
where I'm wondering if code can allow to close the document it is written
in???)

Thank you.
 
Workbooks("name1").Sheets(1).Range("a1:B2").Copy
Workbooks("name2").Sheets(1).Range("a1").paste
Workbooks("name1").Close
 
Hello,

This gives me "Compile Error: Syntax Error". My guess is that it's because
the macro is written to close the workbook it is written in. I don't see how
it's possible to do this, however, maybe you have a suggestion?

What I am attempting to do is have one file named "Current Price Chart" that
will recalculate by the subsequent files that are 1) opened 2) copied
(certain cells) 3) pasted into it 4)and finally closed without saving.

Each of these files that information is copied would have their own macro
(this is how my intermediate level of Excel mind is thinking) that would
trigger upon opening, copy information, and paste it into "Current Price
Chart" then close itself without saving.

Is there another way?

Also, if I am able to create such a macro in each of these files isn't there
also a way to overide the automatic trigger of the macro by holding down a
particular key when the file is opened (I remember seeing something like that
in the discussion group somewhere)?

Your help is appreciated. Thank you.
 
With "price chart template.xls" already open...

Option Explicit
sub Auto_open
dim tempWks as worksheet
set tempwks = nothing
on error resume next
set tempwks = workbooks("price chart template.xls").worksheets(1)
on error goto 0

if tempwks is nothing then
msgbox "Please open the template workbook!"
else
thisworkbook.worksheets(1).range("a1:b2").copy _
destination:=tempwks.range("a1")
end if

thisworkbook.close savechanges:=false
end sub

=========
But this means you have to do something special when you want to open this file
-- just for normal changes.

You can open the workbook with macros disabled or hold down the shift key while
you're opening this workbook.
 
Back
Top