Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro on only one open workbook
I have several workbooks, each with a macro (to rearrange data) that runs "on
open". When I open a second workbook (wk2), the macro runs, then encounters an error when it tries to run (re-run) the macro on the first workbook (wk1). My goal is have the macro fire only on the workbook that I'm opening, and then stop. I'm curious as to why the macro on wk1 tries to run when the open event has not occurred. Do I need some type of command to stop the macro in wk2? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro on only one open workbook
Obviously you are having the macro run on the ActiveWorkbook. You need to
specify the workbook to run it on. ******************* ~Anne Troy www.OfficeArticles.com "Pete Merenda" wrote in message ... I have several workbooks, each with a macro (to rearrange data) that runs "on open". When I open a second workbook (wk2), the macro runs, then encounters an error when it tries to run (re-run) the macro on the first workbook (wk1). My goal is have the macro fire only on the workbook that I'm opening, and then stop. I'm curious as to why the macro on wk1 tries to run when the open event has not occurred. Do I need some type of command to stop the macro in wk2? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro on only one open workbook
probably because it has a reference to that workbook.
Post the code. -- HTH Bob Phillips "Pete Merenda" wrote in message ... I have several workbooks, each with a macro (to rearrange data) that runs "on open". When I open a second workbook (wk2), the macro runs, then encounters an error when it tries to run (re-run) the macro on the first workbook (wk1). My goal is have the macro fire only on the workbook that I'm opening, and then stop. I'm curious as to why the macro on wk1 tries to run when the open event has not occurred. Do I need some type of command to stop the macro in wk2? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro on only one open workbook
The macro, in Module 1 is:
Sub PasteTotalfromADs() Sheets("Taylor").Select ActiveSheet.Unprotect Range("C49:D56").Select Selection.Copy Range("C6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C58:D65").Select Application.CutCopyMode = False ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub In the 'This Workbook' object: Private Sub Workbook_Open() PasteTotalfromADs End Sub Unfortunately, I don't know the code to specify that the macro run only on the active workbook (ActiveWorkbook.Select??). Is that all I need? Do I need an Exit Sub statement? Thanks for your help "Bob Phillips" wrote: probably because it has a reference to that workbook. Post the code. -- HTH Bob Phillips "Pete Merenda" wrote in message ... I have several workbooks, each with a macro (to rearrange data) that runs "on open". When I open a second workbook (wk2), the macro runs, then encounters an error when it tries to run (re-run) the macro on the first workbook (wk1). My goal is have the macro fire only on the workbook that I'm opening, and then stop. I'm curious as to why the macro on wk1 tries to run when the open event has not occurred. Do I need some type of command to stop the macro in wk2? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro on only one open workbook
Anne,
Can you please tell me the language and syntax to select the active workbook. I used the record macro feature in Excel to produce the code, and am not versed in writing it myself. Thanks in advance "Anne Troy" wrote: Obviously you are having the macro run on the ActiveWorkbook. You need to specify the workbook to run it on. ******************* ~Anne Troy www.OfficeArticles.com "Pete Merenda" wrote in message ... I have several workbooks, each with a macro (to rearrange data) that runs "on open". When I open a second workbook (wk2), the macro runs, then encounters an error when it tries to run (re-run) the macro on the first workbook (wk1). My goal is have the macro fire only on the workbook that I'm opening, and then stop. I'm curious as to why the macro on wk1 tries to run when the open event has not occurred. Do I need some type of command to stop the macro in wk2? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro on only one open workbook
Hi, Pete. I'm sorry. I'm no coder, nor do I have the desire ever to be. But
I am a MS Office project manager that requires code from time to time. So a little over a year ago, I was looking for some code that I know I'd seen before, and I got so frustrated 'cause I couldn't find it that I decided...if nobody else is gonna do it, I would. I created www.vbaexpress.com and its knowledgebase (www.vbaexpress.com/kb and you have to be a member of the main site so you can search the kb). It's completely free, and you can put your code there, too (with approval by our Approvers). That'll ensure that you never lose it! You'll get all your VBA questions (regardless of the app) answered there fairly quickly. ******************* ~Anne Troy www.OfficeArticles.com "Pete Merenda" wrote in message ... Anne, Can you please tell me the language and syntax to select the active workbook. I used the record macro feature in Excel to produce the code, and am not versed in writing it myself. Thanks in advance "Anne Troy" wrote: Obviously you are having the macro run on the ActiveWorkbook. You need to specify the workbook to run it on. ******************* ~Anne Troy www.OfficeArticles.com "Pete Merenda" wrote in message ... I have several workbooks, each with a macro (to rearrange data) that runs "on open". When I open a second workbook (wk2), the macro runs, then encounters an error when it tries to run (re-run) the macro on the first workbook (wk1). My goal is have the macro fire only on the workbook that I'm opening, and then stop. I'm curious as to why the macro on wk1 tries to run when the open event has not occurred. Do I need some type of command to stop the macro in wk2? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro on only one open workbook
Sub PasteTotalfromADs()
ThisWorkbook.Activate With thisworkbook.Sheets("Taylor") .Activate .Unprotect .Range("C49:D56").Copy .Range("C6").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False .protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End Sub In the 'This Workbook' object: Private Sub Workbook_Open() PasteTotalfromADs End Sub -- Regards, Tom Ogilvy "Pete Merenda" wrote in message ... The macro, in Module 1 is: Sub PasteTotalfromADs() Sheets("Taylor").Select ActiveSheet.Unprotect Range("C49:D56").Select Selection.Copy Range("C6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C58:D65").Select Application.CutCopyMode = False ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub In the 'This Workbook' object: Private Sub Workbook_Open() PasteTotalfromADs End Sub Unfortunately, I don't know the code to specify that the macro run only on the active workbook (ActiveWorkbook.Select??). Is that all I need? Do I need an Exit Sub statement? Thanks for your help "Bob Phillips" wrote: probably because it has a reference to that workbook. Post the code. -- HTH Bob Phillips "Pete Merenda" wrote in message ... I have several workbooks, each with a macro (to rearrange data) that runs "on open". When I open a second workbook (wk2), the macro runs, then encounters an error when it tries to run (re-run) the macro on the first workbook (wk1). My goal is have the macro fire only on the workbook that I'm opening, and then stop. I'm curious as to why the macro on wk1 tries to run when the open event has not occurred. Do I need some type of command to stop the macro in wk2? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro on only one open workbook
Tom,
Thanks for your help, but unfortunately this code does not accomplish the goal of my original question. When I open more than one Excel wkbk with this code running on open, it runs on all open workbooks, thus running in a loop until it runs into a stacking space error. What command is necessary for the code to stop after running only on the workbook that has been called to open? Thanks "Tom Ogilvy" wrote: Sub PasteTotalfromADs() ThisWorkbook.Activate With thisworkbook.Sheets("Taylor") .Activate .Unprotect .Range("C49:D56").Copy .Range("C6").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False .protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End Sub In the 'This Workbook' object: Private Sub Workbook_Open() PasteTotalfromADs End Sub -- Regards, Tom Ogilvy "Pete Merenda" wrote in message ... The macro, in Module 1 is: Sub PasteTotalfromADs() Sheets("Taylor").Select ActiveSheet.Unprotect Range("C49:D56").Select Selection.Copy Range("C6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C58:D65").Select Application.CutCopyMode = False ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub In the 'This Workbook' object: Private Sub Workbook_Open() PasteTotalfromADs End Sub Unfortunately, I don't know the code to specify that the macro run only on the active workbook (ActiveWorkbook.Select??). Is that all I need? Do I need an Exit Sub statement? Thanks for your help "Bob Phillips" wrote: probably because it has a reference to that workbook. Post the code. -- HTH Bob Phillips "Pete Merenda" wrote in message ... I have several workbooks, each with a macro (to rearrange data) that runs "on open". When I open a second workbook (wk2), the macro runs, then encounters an error when it tries to run (re-run) the macro on the first workbook (wk1). My goal is have the macro fire only on the workbook that I'm opening, and then stop. I'm curious as to why the macro on wk1 tries to run when the open event has not occurred. Do I need some type of command to stop the macro in wk2? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run a macro when I open a workbook | Excel Discussion (Misc queries) | |||
How to Run Macro in open workbook/s | Excel Discussion (Misc queries) | |||
run macro on workbook open | Excel Discussion (Misc queries) | |||
Macro to open another workbook | Excel Programming | |||
How to run VBA macro when the workbook is open? | Excel Programming |