☰

在多张表上运行宏

tag-icon • tag-icon microsoft-excel-2010 vba
在多张表上运行宏

我可以在单个工作表上运行宏，但不能在所有打开的工作表上运行宏。

我想在 700 个工作表上运行此宏。我们正在尝试删除一列。

Sub RunMacroOnAllSheetsToRight()
    For i = ActiveSheet.Index To Sheets.Count
        Call MyFunction(i)
    Next i
End Sub

Function MyFunction(i)
    'Code goes here

    Dim lColumn As Long
    Dim iCntr As Long
    lColumn = 5
    'For Each ws In ThisWorkbook.Worksheets

    'For iCntr = lColumn To 1 Step -1
        If IsNumeric(Cells(1, lColumn)) Then
            Columns(lColumn).Delete
        End If
    'Next ws

    MsgBox "I'm currently on sheet " & ThisWorkbook.Sheets(i).Name
End Function

答案1

我修改了我的函数使其工作：

Function MyFunction(i)
    'Code goes here
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets(i)
    Dim lColumn As Long
    Dim iCntr As Long
    lColumn = 5
    'For Each ws In ThisWorkbook.Worksheets

    'For iCntr = lColumn To 1 Step -1
        wks.Activate
        If IsNumeric(wks.Cells(1, lColumn)) Then
            wks.Range(Cells(1, lColumn), Cells(1, lColumn)).EntireColumn.Delete
        End If
    'Next ws

    MsgBox "I'm currently on sheet " & ThisWorkbook.Sheets(i).Name
    Set wks = Nothing
    Set wkb = Nothing
End Function

我添加了变量wkb并wks对应于工作簿和工作表。使用这些变量可以轻松引用工作表的单元格并删除所需的列。

如果你需要在 700 个工作表上执行它，你需要删除消息框。另外，禁用和启用ScreenUpdating将使其运行得更快。

相关内容