Can you link two macro's together

[alexb]

I have two macro's that I have written and they work. Once macro 1 has finished i want macro two to start. Is this possilbe?

 

[Mudface]

Before the final 'End Sub' in your first macro add the line

Call Macro2 ' change to whatever your second Macro is called

You can just use Macro2 without the Call, if you like, I tend to leave it in as it shows up what you're doing a bit more readily.

 

[Guest]

That is fine (and works) but I have written this in the This Workbook part of VBA and can't name the macro - would typing
Call Thisworkbook do the trick?

 

[Mudface]

Don't think so. I presume you've put the macro code into the Workbook_Open() sub so try the following:-

Private Sub Workbook_Open()

Call Macro1
Call Macro2

End Sub

Insert a module and cut and paste your two macro's into it. You'll probably need to name your first macro, just put: -

Public Sub Macro1()

before your code and make sure there's an End Sub after it. HTH.

 

[Guest]

Doesn't want to do it as I need my macro to run first. I need the work book_open() to run at the end of Macro1

 

[Mudface]

Could you post your code, please, as I don't quite get what you're driving at. The workbook open routine runs due to the workbook open event and therefore, by definition, should run before any other macros and shouldn't need to be called.

 

[Guest]

Here we go then:
This is my first macro:
Sub emailnew2()
'
' emailnew2 Macro
' Macro recorded 3/7/2002 by Base-Displays
'

'
Sheets("sheet11").Select
Range("E22:E79").Select
Selection.ClearContents
Sheets("Sheet 1").Select
Range("E11:E68").Select
Selection.Copy
Sheets("sheet11").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=85
Range("E95:E118").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Sheet 1").Select
ActiveWindow.SmallScroll Down:=73
Range("E82:E105").Select
Selection.Copy
Sheets("sheet11").Select
ActiveSheet.Paste
ActiveWindow.ScrollRow = 1
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select
ActiveWindow.Zoom = 75
Application.CutCopyMode = False
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

When the new sheet is opened I want to display my special offer Stop press window (this is a price I can send to customers)that will pop up before they see the whole work book - does this all make sense?

 

[Guest]

Think so . I'm presuming the message you want to show is in the workbook_open sub of the initial workbook, yes?

If so, go to the module in your post above and click on insert- procedure and name the procedure Macro 2, for example.

Go to your workbook_open sub and cut and paste the code in there (minus the workbook_open() and end sub bits) into the new Macro2 procedure. Add Call Macro2 to your Workbook_Open sub and add the same to your emailnew2 sub at the end.

Your code should now look like: -

Private Sub Workbook_Open()

Call Macro2

End Sub

And in the module: -

Sub emailnew2()
'
' emailnew2 Macro
' Macro recorded 3/7/2002 by Base-Displays
'

'
' .... all your code as above

Call Macro2

End Sub


Public Sub Macro2()

'your code

End Sub

Note that the code you have in Macro2 will not be propagated to the new workbook, however...