run macro on only one open workbook

[Guest]

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?

 

[Anne Troy]

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

 

[Bob Phillips]

probably because it has a reference to that workbook.

Post the code.

 

[Guest]

The macro, in Module 1 is:
Sub PasteTotalfromADs()
Sheets("Taylor").Select
ActiveSheet.Unprotect
Range("C4956").Select
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C5865").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

 

[Guest]

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]

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

 

[Tom Ogilvy]

Sub PasteTotalfromADs()
ThisWorkbook.Activate
With thisworkbook.Sheets("Taylor")
.Activate
.Unprotect
.Range("C4956").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

 

[Guest]

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