在多个纸上应用范围修改

Question

我有一个带有12张床单的工作簿(每个月的缩写名称(以及两个额外的支持表。我想创建一个宏，我可以在其中定义许多不同的范围(在下面的示例中，有5个范围(，将它们分组为一个数组，并在所有月度工作表中逐个合并。我遇到了以下代码(在没有错误的情况下运行，显然在我要求的所有工作表中运行( - 但仅在第一个工作表(" Jan"(上应用转换，而对其他工作表进行了任何操作？您能帮我找到我的错误吗？预先感谢大家！

Sub layout()
Dim rng1, rng2, rng3, rng4, rng5 As Range
Set rng1 = Range("A2:C3")
Set rng2 = Range("A4:A5")
Set rng3 = Range("B4:B5")
Set rng4 = Range("C4:C5")
Set rng5 = Range("D2:D5")
Dim arr As Variant
arr = Array(rng1, rng2, rng3, rng4, rng5)
Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Dim ws As Worksheet
Dim i As Integer
For Each ws In wb.Sheets
    Select Case ws.name
    Case Is = "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
        For i = 0 To 4
        ws.Activate
        arr(i).Merge
        Next
    End Select
Next ws
End Sub

Answer 1

我不知道这2个额外的支持表的名称，让我们称它们为SupoortSheet1和supportsheet 2.在这两种情况下使用SELECT CASE不做任何事情，以及任何其他情况，您合并：

For Each ws In wb.Sheets
    Select Case ws.Name
        Case "SupportSheet1"
            'do nothing
        Case "SupportSheet2"
            'do nothing
        Case Else
            'it's a month sheet. We merge
            For i = 0 To 4
                ws.Activate
                arr(i).Merge
            Next
    End Select
Next ws

只是有关案例的更多信息(有时确实有用(，请阅读

Answer 2

您的案例语句明确畸形，并激活工作表以继承主动工作表，因为默认的父工作表应在循环外部。

但是，主要问题是您正在设置范围对象。更改活动表不会重写这些范围对象的父工作表。尽管更改了主动工作表，但设置的父工作表仍将保留。

解决方案是将地址字符串驱动并构造范围。

Option Explicit
Sub layout()
    Dim arr As Variant, wb As Workbook, ws As Worksheet, i As Integer
    arr = Array("A2:C3", "A4:A5", "B4:B5", "C4:C5", "D2:D5")
    Set wb = Application.Workbooks("Book1")
    For Each ws In wb.Worksheets
        Select Case ws.Name
            Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
                For i = 0 To 4
                    ws.Range(arr(i)).Merge
                Next
        End Select
    Next ws
End Sub