时间戳

首页 > 解决方案 > 如何将多个宏粘合在一起？

excel - 如何将多个宏粘合在一起？

问题描述

我为 Excel 编写了三个宏。它们就像我想要的那样工作 - 但我不想单独执行它们（它们为几个文件执行它们）。有人可以帮我将这些宏粘合在一起以使我的工作更轻松吗？

下面是一个按我想要的方式工作的代码。它分为三个单独的宏。

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

我想请你帮我把这三个宏合二为一。

标签： excelvba

解决方案

您的最终宏必须以

Sub ETAP1()

并以

End Sub

只需删除Sub ETAP2(),Sub ETAP3()和End Sub中间的。

您的最终宏将如下所示：

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

另外检查如何避免在 Excel VBA 中使用 Select以使您的代码更清晰、更快速、更易于管理

推荐阅读