CODEWITHSUNDEEP
excelvba
Jakub Bidziński
Jakub Bidziński

Reputation: 143

How to glue several macros together?

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.

Upvotes: 0

Views: 63

Answers (2)

U7765660
U7765660

Reputation: 91

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

Like the below :

Sub ETAP()
    ETAP1
    ETAP2
    ETAP3
End Sub

Upvotes: 0

Pierre44
Pierre44

Reputation: 1741

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

Upvotes: 1

Related Questions