How to glue several macros together?

Question

I have three macros written for Excel. They work as if I wanted to - but I don't want to do them separately (they execute them for a few files). Could someone help me glue these macros together to make my work easier?

Below is a code that works the way I want it to. It is broken down into three separate macros.

Sub ETAP1()
'
' ETAP1 Makro
'

'
    ActiveSheet.Unprotect
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Range("I12").Select
    ActiveSheet.ShowAllData
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=10, Criteria1 _
        :="PROGNOZA_05_2019"
End Sub



Sub ETAP2()
'
' ETAP2 Makro
'

'
    Selection.Replace What:="PROGNOZA_05_2019", Replacement:="PROGNOZA_06_2019" _
        , LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
        :=False, ReplaceFormat:=False
    'Columns("K:U").Select
    'Selection.EntireColumn.Hidden = True
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=10, Criteria1 _
        :="PROGNOZA_06_2019"


End Sub

Sub ETAP3()
'
' ETAP3 Makro
'

'
    Columns("K:U").Select
    ActiveWindow.SmallScroll ToRight:=12
    Range("K:U,AZ:BJ,BL:CA,CC:CO,CQ:DC,DE:DQ,DS:EE").Select
    Range("DS1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveSheet.ListObjects("T_BGT_104_2").Range.AutoFilter Field:=136, _
        Criteria1:="1,00"
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 80
    ActiveWindow.ScrollColumn = 63
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 1
    Sheets("A_BGT_104-2").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    ActiveWorkbook.Save
End Sub

I would ask you to help me combine these three macros into one.

Read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Tim Stack, Jun 24 '19 at 07:58

score 1 · Accepted Answer · answered Jun 24 '19 at 08:34

Your final Macro has to start with

Sub ETAP1()

and end with

End Sub

Just delete the Sub ETAP2(), Sub ETAP3() and the End Sub in between.

Your final Macro will look like this:

Sub ETAP1()
'
' ETAP1 Makro
'

'
    ActiveSheet.Unprotect
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Range("I12").Select
    ActiveSheet.ShowAllData
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=10, Criteria1 _
        :="PROGNOZA_05_2019"

    Selection.Replace What:="PROGNOZA_05_2019", Replacement:="PROGNOZA_06_2019" _
        , LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
        :=False, ReplaceFormat:=False
    'Columns("K:U").Select
    'Selection.EntireColumn.Hidden = True
    ActiveSheet.ListObjects(1).Range.AutoFilter Field:=10, Criteria1 _
        :="PROGNOZA_06_2019"

   Columns("K:U").Select
    ActiveWindow.SmallScroll ToRight:=12
    Range("K:U,AZ:BJ,BL:CA,CC:CO,CQ:DC,DE:DQ,DS:EE").Select
    Range("DS1").Activate
    Selection.EntireColumn.Hidden = True
    ActiveSheet.ListObjects("T_BGT_104_2").Range.AutoFilter Field:=136, _
        Criteria1:="1,00"
    ActiveWindow.ScrollColumn = 94
    ActiveWindow.ScrollColumn = 80
    ActiveWindow.ScrollColumn = 63
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 1
    Sheets("A_BGT_104-2").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
    ActiveWorkbook.Save
End Sub

Additionally check the How to avoid using Select in Excel VBA to make your code cleaner, faster and easier to manage

score 0 · Answer 2 · edited Jun 25 '19 at 06:12

0

Add a new sub and then call ETAP1, ETAP2 and ETAP3.

Like the below :

Sub ETAP()
    ETAP1
    ETAP2
    ETAP3
End Sub

edited Jun 25 '19 at 06:12

AJD

2,400
2
12
22

answered Jun 24 '19 at 12:07

U7765660

91
6

This would cause a [syntax-error](https://learn.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/syntax-error). [Parentheses](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-parentheses-in-code) are only used when returning something. Also, asking the user to accept your answer in your answer is probably not the best etiquette for this site. – Robert Todar Jun 24 '19 at 16:01
@RobertTodar, thanks for your input, modified my answer accordingly. – U7765660 Jun 25 '19 at 06:15

How to glue several macros together?

2 Answers2