☰

如何让该宏在打开的工作表上运行？

tag-icon • tag-icon microsoft-excel vba
如何让该宏在打开的工作表上运行？

宏会删除我不需要的列，方法是删除除指定名称的列之外的所有列。

我无法弄清楚（应该很容易）如何让它在任何工作表上工作，而不是具有特定名称的工作表。宏存储在 personal.xlsb 中。我该怎么做？

Sub DeleteUnnecessaryColumns()

Dim currentSht As Worksheet
Dim i As Long, j As Long
Dim lastRow As Long, lastCol As Long
Dim startCell As Range
Dim colnames
Dim here As Boolean

colnames = Array("first column I want to keep", "Second column I want to keep", "goes on for ages")

Set currentSht = ActiveWorkbook.Sheets("export")
Set startCell = currentSht.Range("A1")

lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column

With currentSht
    For i = lastCol To 1 Step -1
        here = False
        For j = LBound(colnames) To UBound(colnames)
            If .Cells(1, i).Value = colnames(j) Then
                here = True
                Exit For
            End If
        Next j
        If Not here Then
            Columns(i).EntireColumn.Delete
        End If
    Next i
End With

End Sub

答案1

要使宏在活动工作表上运行，请替换：

Set currentSht = ActiveWorkbook.Sheets("export")

和：

Set currentSht = ActiveSheet

相关内容