Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro Upon Opening Then Close
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. -- David P. |
#2
|
|||
|
|||
Workbooks("name1").Sheets(1).Range("a1:B2").Copy
Workbooks("name2").Sheets(1).Range("a1").paste Workbooks("name1").Close "David P." wrote in message ... 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. -- David P. |
#3
|
|||
|
|||
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. -- David P. "PY & Associates" wrote: Workbooks("name1").Sheets(1).Range("a1:B2").Copy Workbooks("name2").Sheets(1).Range("a1").paste Workbooks("name1").Close "David P." wrote in message ... 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. -- David P. |
#4
|
|||
|
|||
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. David P. wrote: 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. -- David P. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run macro when opening workbook | Excel Discussion (Misc queries) | |||
Opening a file in a macro (network) | Excel Discussion (Misc queries) | |||
macro to close excel | Excel Discussion (Misc queries) | |||
Macro warning (upon opening) | Excel Discussion (Misc queries) | |||
After deleting a macro, I still get the pop-up when opening file | Excel Worksheet Functions |