ActiveWorkbook pointer for Macros from PERSONAL.XLS

R

Robert

I am now trying to run all macros FROM Personal.xls. Having followed some of
the posts, I tried but I am still unable to point to the worksheet that has
to be run.
Seek help (Norman Jones? Ron de Bruin?) Thank you.
Examples of my macros:
Macro1
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
Calculate
Application.Run "PERSONAL.XLS!Sort1"
Application.Run "PERSONAL.XLS!Sort2"
Range("G2").Select
Calculate
End Sub
====
Macro Sort1 (only the beginning quoted here)

Application.Goto Reference:="STARTSORT1"
Range("J1:J2000").Select
Selection.Copy
Application.Goto Reference:="R11001C11"
............End Sub
=====
MacroSort2 (only beginning quoted here)

Dim i As Long
Application.Goto Reference:="STARTSORT2"
Range("A1:IR2000").Copy
Range("A11000").PasteSpecial Paste:=xlPasteValues, _
.......End Sub
 
N

Norman Jones

Hi Robert,
I am now trying to run all macros FROM Personal.xls. Having followed some
of
the posts, I tried but I am still unable to point to the worksheet that
has
to be run.

One way :

Add an argument (ShtName) to the sort1 routine in Personal.xls:

Sub Sort1(ShtName As String)
Dim sh As Worksheet

Set sh = ActiveWorkbook.Sheets(shtName)

' Your procesing code

End Sub

You can then pass the name of the requisite sheet to the Sort1 routine from
the call statement, e.g.:

Sub Macro1()

'Do something
Application.Run "Personal.xls!Sort1", "MySheet"
'Do something elae

End Sub
 
N

Norman Jones

Hi robert,
You can then pass the name of the requisite sheet to the Sort1 routine
from the call statement, e.g.:

Should read:

You can then pass the name of the requisite sheet to the Sort1 routine from
the Run statement, e.g.:
 
R

Robert

Thank you Norman. I am a novice, have tried the whole morning to put in your
suggestion but I just cannot get it. Now looking up for similar examples.
When I
do get it right, I shall let you know.
--
Robert


Norman Jones said:
Hi robert,
You can then pass the name of the requisite sheet to the Sort1 routine
from the call statement, e.g.:

Should read:

You can then pass the name of the requisite sheet to the Sort1 routine from
the Run statement, e.g.:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top