excel - 如何将此 VBA 代码应用于两个指定的工作表？

Question

我是 VBA 的新手，我需要帮助将以下 VBA 代码应用于两个特定的工作表 by Region和 by Model .代码只查找名称为 total for the year 的最后一列。并将前几个月的值复制到一个新列中。如果分别在每个工作表上执行代码，则代码可以正常工作。

Sub Insert_New_Col()
Dim R As Range, BeforeR As Long


'Find 'Totals' in row 5 of active sheet
Set R = Rows(3).Find(what:="Total for the Year", lookat:=xlWhole)

'identify the column to copy (last month)
BeforeR = R.Column - 1


    If R Is Nothing Then
      MsgBox ("The word 'Totals' was not found in Row 5 - macro terminated!")
        Exit Sub
    ElseIf Not R Is Nothing Then

    'copy last month's column
    Columns(BeforeR).Copy

    'insert copied cells before the Totals column
    Columns(R.Column).Insert Shift:=xlRight


    Application.CutCopyMode = False
    End If

End Sub

Answer 1

创建要循环的工作表数组。通过这种设置方式，您可以根据需要向阵列添加任意数量的工作表，而无需修改宏。您需要修改您的 msgbox因为当它无法找到指定值时您不再退出子 - 相反，您继续前进到下一张纸。

Sub Insert_New_Col()

Dim Found As Range, BeforeR As Long
Dim ws As Worksheet, xSheets As Variant
Dim i As Long

xSheets = Array("Sheet1", "Sheet2") '<-- List of sheets to loop through

For i = LBound(xSheets) To UBound(xSheets)

    Set ws = xSheets(i)
    Set Found = ws.Rows(3).Find(What:="Total for the Year", Lookat:=xlWhole)
    BeforeR = R.Column - 1

    If Found Is Nothing Then
        MsgBox ("The word 'Totals' was not found in Row 5 on Sheet: " & ws.Name)
    Else
        Columns(BeforeR).Copy
        ws.Columns(R.Column).Insert Shift:=xlRight
    End If
Next i

End Sub