Hi folks,

I have two workbooks - Book1 and Book2.

In Book1 I have Macro1,

In Book2 I also have a Macro1 (Or I can name it differently if necessary)
and also Macro2 and Macro3 and Macro4 and....etc etc

I want Macro1 in Book1 to open Book2 and then after opening to automatically
execute Book2's Macro1 ?

Sometimes I might want it to execute a different one of the Macros in Book2
automatically after opening so I can't just include it as part of an
auto-open...I think...

Please Help!!!....this is a very substantial stumbling block for my current
file if I can't get over it.... :-(

Set WB = Workbooks.Open "Book2.xls"
Application.Run "'Book2.xls'!Macro1"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"dim" <(E-Mail Removed)> wrote in message
news:BC1C6F7B-231E-4608-9616-(E-Mail Removed)...
> Hi folks,
>
> I have two workbooks - Book1 and Book2.
>
> In Book1 I have Macro1,
>
> In Book2 I also have a Macro1 (Or I can name it differently if necessary)
> and also Macro2 and Macro3 and Macro4 and....etc etc
>
> I want Macro1 in Book1 to open Book2 and then after opening to
> automatically
> execute Book2's Macro1 ?
>
> Sometimes I might want it to execute a different one of the Macros in
> Book2
> automatically after opening so I can't just include it as part of an
> auto-open...I think...
>
> Please Help!!!....this is a very substantial stumbling block for my
> current
> file if I can't get over it.... :-(
>
>

Typo alert:
Set WB = Workbooks.Open "Book2.xls"
should be:
Set WB = Workbooks.Open("Book2.xls")




Bob Phillips wrote:
>
> Set WB = Workbooks.Open "Book2.xls"
> Application.Run "'Book2.xls'!Macro1"
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "dim" <(E-Mail Removed)> wrote in message
> news:BC1C6F7B-231E-4608-9616-(E-Mail Removed)...
> > Hi folks,
> >
> > I have two workbooks - Book1 and Book2.
> >
> > In Book1 I have Macro1,
> >
> > In Book2 I also have a Macro1 (Or I can name it differently if necessary)
> > and also Macro2 and Macro3 and Macro4 and....etc etc
> >
> > I want Macro1 in Book1 to open Book2 and then after opening to
> > automatically
> > execute Book2's Macro1 ?
> >
> > Sometimes I might want it to execute a different one of the Macros in
> > Book2
> > automatically after opening so I can't just include it as part of an
> > auto-open...I think...
> >
> > Please Help!!!....this is a very substantial stumbling block for my
> > current
> > file if I can't get over it.... :-(
> >
> >

--

Dave Peterson

Thankyou very much,

That helps but I think I didn't explain enough. I'm having trouble with
specifying which Macro to run also. I've been trying variations of IF THEN
ELSEIF as below but I keep getting errors!

Macro1 in Book1 should decided whether to run Macro1/2/3etc in Book2
dependant upon the value of a cell (Cell A1 in the example below) in Book1.

I've tried putting variations of this into the code after the workbook
opening code but it isn't working:

If .Value(1,1) = 1 .Then .Application.Run "Macro1"
Elseif .Value(1,1) = 2 .Then .Application.Run "Macro2"
Elseif etc etc

Where am I going wrong?

:-(

"Dave Peterson" wrote:

> Typo alert:
> Set WB = Workbooks.Open "Book2.xls"
> should be:
> Set WB = Workbooks.Open("Book2.xls")
>
>
>
>
> Bob Phillips wrote:
> >
> > Set WB = Workbooks.Open "Book2.xls"
> > Application.Run "'Book2.xls'!Macro1"
> >
> > --
> > ---
> > HTH
> >
> > Bob
> >
> > (there's no email, no snail mail, but somewhere should be gmail in my addy)
> >
> > "dim" <(E-Mail Removed)> wrote in message
> > news:BC1C6F7B-231E-4608-9616-(E-Mail Removed)...
> > > Hi folks,
> > >
> > > I have two workbooks - Book1 and Book2.
> > >
> > > In Book1 I have Macro1,
> > >
> > > In Book2 I also have a Macro1 (Or I can name it differently if necessary)
> > > and also Macro2 and Macro3 and Macro4 and....etc etc
> > >
> > > I want Macro1 in Book1 to open Book2 and then after opening to
> > > automatically
> > > execute Book2's Macro1 ?
> > >
> > > Sometimes I might want it to execute a different one of the Macros in
> > > Book2
> > > automatically after opening so I can't just include it as part of an
> > > auto-open...I think...
> > >
> > > Please Help!!!....this is a very substantial stumbling block for my
> > > current
> > > file if I can't get over it.... :-(
> > >
> > >
>
> --
>
> Dave Peterson
>

It is not Dot Application. Application is the top of the OM, so it cannot be
dot qualified.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"dim" <(E-Mail Removed)> wrote in message
news:781D23AD-B004-40E3-A061-(E-Mail Removed)...
> Thankyou very much,
>
> That helps but I think I didn't explain enough. I'm having trouble with
> specifying which Macro to run also. I've been trying variations of IF THEN
> ELSEIF as below but I keep getting errors!
>
> Macro1 in Book1 should decided whether to run Macro1/2/3etc in Book2
> dependant upon the value of a cell (Cell A1 in the example below) in
> Book1.
>
> I've tried putting variations of this into the code after the workbook
> opening code but it isn't working:
>
> If .Value(1,1) = 1 .Then .Application.Run "Macro1"
> Elseif .Value(1,1) = 2 .Then .Application.Run "Macro2"
> Elseif etc etc
>
> Where am I going wrong?
>
> :-(
>
> "Dave Peterson" wrote:
>
>> Typo alert:
>> Set WB = Workbooks.Open "Book2.xls"
>> should be:
>> Set WB = Workbooks.Open("Book2.xls")
>>
>>
>>
>>
>> Bob Phillips wrote:
>> >
>> > Set WB = Workbooks.Open "Book2.xls"
>> > Application.Run "'Book2.xls'!Macro1"
>> >
>> > --
>> > ---
>> > HTH
>> >
>> > Bob
>> >
>> > (there's no email, no snail mail, but somewhere should be gmail in my
>> > addy)
>> >
>> > "dim" <(E-Mail Removed)> wrote in message
>> > news:BC1C6F7B-231E-4608-9616-(E-Mail Removed)...
>> > > Hi folks,
>> > >
>> > > I have two workbooks - Book1 and Book2.
>> > >
>> > > In Book1 I have Macro1,
>> > >
>> > > In Book2 I also have a Macro1 (Or I can name it differently if
>> > > necessary)
>> > > and also Macro2 and Macro3 and Macro4 and....etc etc
>> > >
>> > > I want Macro1 in Book1 to open Book2 and then after opening to
>> > > automatically
>> > > execute Book2's Macro1 ?
>> > >
>> > > Sometimes I might want it to execute a different one of the Macros in
>> > > Book2
>> > > automatically after opening so I can't just include it as part of an
>> > > auto-open...I think...
>> > >
>> > > Please Help!!!....this is a very substantial stumbling block for my
>> > > current
>> > > file if I can't get over it.... :-(
>> > >
>> > >
>>
>> --
>>
>> Dave Peterson
>>

Thanks again Bob,

I was hoping not to bore everyone with details but I'm still stuck, and I
REALLY appreciate the help, so here is examples of what I'm using for
everything! I tried to incorporate what help I've been given so far but it's
still stuck....thanks again if you can have a look at this Bob, or anyone
else,

My Macro1 in Book1:

Sub Macro1()
'
' Macro1 Macro
'

'
If .Value(1, 1) = 1 Then
Set WB = Workbooks.Open "C:\Documents and Settings\D. Murphy\My
Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro1"
ElseIf .Value(1, 1) = 2 Then
Set WB = Workbooks.Open "C:\Documents and Settings\D. Murphy\My
Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro2"
ElseIf .Value(1, 1) = 3 Then
Set WB = Workbooks.Open "C:\Documents and Settings\D. Murphy\My
Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro3"
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

My Macro1, 2 & 3 in Book2:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("B1").Select
Selection.ClearContents
Range("C1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A1").Select
End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
Range("B2").Select
Selection.ClearContents
Range("C2").Select
ActiveCell.FormulaR1C1 = "2"
Range("A1").Select
End Sub
Sub Macro3()
'
' Macro3 Macro
'

'
Range("B3").Select
Selection.ClearContents
Range("C3").Select
ActiveCell.FormulaR1C1 = "3"
Range("A1").Select
End Sub

Where's the End If?

"dim" <(E-Mail Removed)> wrote in message
news:B9C3F9ED-6982-4948-82FA-(E-Mail Removed)...
> Thanks again Bob,
>
> I was hoping not to bore everyone with details but I'm still stuck, and I
> REALLY appreciate the help, so here is examples of what I'm using for
> everything! I tried to incorporate what help I've been given so far but
> it's
> still stuck....thanks again if you can have a look at this Bob, or anyone
> else,
>
> My Macro1 in Book1:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> '
>
> '
> If .Value(1, 1) = 1 Then
> Set WB = Workbooks.Open "C:\Documents and Settings\D. Murphy\My
> Documents\Book2.xls"
> Application.Run "'Book2.xls'!Macro1"
> ElseIf .Value(1, 1) = 2 Then
> Set WB = Workbooks.Open "C:\Documents and Settings\D. Murphy\My
> Documents\Book2.xls"
> Application.Run "'Book2.xls'!Macro2"
> ElseIf .Value(1, 1) = 3 Then
> Set WB = Workbooks.Open "C:\Documents and Settings\D. Murphy\My
> Documents\Book2.xls"
> Application.Run "'Book2.xls'!Macro3"
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> End Sub
>
> My Macro1, 2 & 3 in Book2:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> '
>
> '
> Range("B1").Select
> Selection.ClearContents
> Range("C1").Select
> ActiveCell.FormulaR1C1 = "1"
> Range("A1").Select
> End Sub
> Sub Macro2()
> '
> ' Macro2 Macro
> '
>
> '
> Range("B2").Select
> Selection.ClearContents
> Range("C2").Select
> ActiveCell.FormulaR1C1 = "2"
> Range("A1").Select
> End Sub
> Sub Macro3()
> '
> ' Macro3 Macro
> '
>
> '
> Range("B3").Select
> Selection.ClearContents
> Range("C3").Select
> ActiveCell.FormulaR1C1 = "3"
> Range("A1").Select
> End Sub
>
>

Woohoo! I have it! And its all the sweeter because I sorted it out before
anyone got back to me! :-D

Thanks very much Bob & Dave, I couldn't have got it thus far without your
help. I hope everyone had a happy Christmas or whatever other festival you
may celebrate. :-)

Bye for now. Thanks.

Here's what I used in case anyone reads this in future:

Sub Macro1()
'
' Macro1 Macro
'

'
ScreenUpdating = False
If (Cells(1, 1).Value) = 1 Then
Workbooks.Open Filename:= _
"C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro1"
ActiveWorkbook.Save
ActiveWorkbook.Close
ElseIf (Cells(1, 1).Value) = 2 Then
Workbooks.Open Filename:= _
"C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro2"
ActiveWorkbook.Save
ActiveWorkbook.Close
ElseIf (Cells(1, 1).Value) = 3 Then
Workbooks.Open Filename:= _
"C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro3"
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
ScreenUpdating = True
End Sub

A bit cleaner

Sub Macro1()
Application.ScreenUpdating = False
With Cells(1, 1)
If .Value > 0 And .Value < 4 Then

Workbooks.Open Filename:= _
"C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"

Select Case .Value

Case 1: Application.Run "'Book2.xls'!Macro1"
Case 2: Application.Run "'Book2.xls'!Macro2"
Case 3: Application.Run "'Book2.xls'!Macro3"
End Select
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End With
Application.ScreenUpdating = True
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"dim" <(E-Mail Removed)> wrote in message
news:E6A4ED59-77B1-4864-AC74-(E-Mail Removed)...
> Woohoo! I have it! And its all the sweeter because I sorted it out before
> anyone got back to me! :-D
>
> Thanks very much Bob & Dave, I couldn't have got it thus far without your
> help. I hope everyone had a happy Christmas or whatever other festival you
> may celebrate. :-)
>
> Bye for now. Thanks.
>
> Here's what I used in case anyone reads this in future:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> '
>
> '
> ScreenUpdating = False
> If (Cells(1, 1).Value) = 1 Then
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
> Application.Run "'Book2.xls'!Macro1"
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> ElseIf (Cells(1, 1).Value) = 2 Then
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
> Application.Run "'Book2.xls'!Macro2"
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> ElseIf (Cells(1, 1).Value) = 3 Then
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
> Application.Run "'Book2.xls'!Macro3"
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> End If
> ScreenUpdating = True
> End Sub

GREAT! :-) I'll change it to that. Yours looks much better. thankyou.

"Bob Phillips" wrote:

> A bit cleaner
>
> Sub Macro1()
> Application.ScreenUpdating = False
> With Cells(1, 1)
> If .Value > 0 And .Value < 4 Then
>
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
>
> Select Case .Value
>
> Case 1: Application.Run "'Book2.xls'!Macro1"
> Case 2: Application.Run "'Book2.xls'!Macro2"
> Case 3: Application.Run "'Book2.xls'!Macro3"
> End Select
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> End If
> End With
> Application.ScreenUpdating = True
> End Sub
>
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
>
>
> "dim" <(E-Mail Removed)> wrote in message
> news:E6A4ED59-77B1-4864-AC74-(E-Mail Removed)...
> > Woohoo! I have it! And its all the sweeter because I sorted it out before
> > anyone got back to me! :-D
> >
> > Thanks very much Bob & Dave, I couldn't have got it thus far without your
> > help. I hope everyone had a happy Christmas or whatever other festival you
> > may celebrate. :-)
> >
> > Bye for now. Thanks.
> >
> > Here's what I used in case anyone reads this in future:
> >
> > Sub Macro1()
> > '
> > ' Macro1 Macro
> > '
> >
> > '
> > ScreenUpdating = False
> > If (Cells(1, 1).Value) = 1 Then
> > Workbooks.Open Filename:= _
> > "C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
> > Application.Run "'Book2.xls'!Macro1"
> > ActiveWorkbook.Save
> > ActiveWorkbook.Close
> > ElseIf (Cells(1, 1).Value) = 2 Then
> > Workbooks.Open Filename:= _
> > "C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
> > Application.Run "'Book2.xls'!Macro2"
> > ActiveWorkbook.Save
> > ActiveWorkbook.Close
> > ElseIf (Cells(1, 1).Value) = 3 Then
> > Workbooks.Open Filename:= _
> > "C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
> > Application.Run "'Book2.xls'!Macro3"
> > ActiveWorkbook.Save
> > ActiveWorkbook.Close
> > End If
> > ScreenUpdating = True
> > End Sub
>
>
>