VBA多工作表列顺序调整及列删除宏技术咨询

Let's tackle your VBA needs step by step—first we'll wrap up your column-deletion macro, then build the cross-worksheet column reordering tool you're looking for.

1. Completed Column Deletion Macro #

Your existing macro was nearly finished—here's the full, tested version that removes all columns except "Employee Number" and "Status":

Sub testDelete()
    Dim currentColumn As Integer
    Dim columnHeading As String
    Dim ws1 As Worksheet
    
    Set ws1 = ActiveWorkbook.Sheets("mySheet")
    ws1.Activate
    
    ' Loop from last column to first to avoid skipping columns when deleting
    With ws1
        For currentColumn = .UsedRange.Columns.Count To 1 Step -1
            columnHeading = .UsedRange.Cells(1, currentColumn).Value
            
            ' Check if we need to keep the column
            Select Case columnHeading
                Case "Employee Number", "Status"
                    ' Do nothing—retain these columns
                Case Else
                    ' Delete any column not in our keep list
                    .Columns(currentColumn).Delete
            End Select
        Next currentColumn
    End With
End Sub

Quick Tips for This Macro: #

• Looping from last to first column prevents column-shift issues—if we deleted left-to-right, we'd skip columns as the sheet adjusts.
• The With ws1 block makes the code cleaner and faster by cutting down on repeated worksheet references.

2. Cross-Worksheet Column Reordering Macro #

This macro lets you define a custom column order, then applies that order to multiple worksheets (you can target specific sheets or every sheet in the workbook).

Full Macro Code: #

Sub ReorderColumnsAcrossSheets()
    Dim targetColumns As Variant
    Dim ws As Worksheet
    Dim colIndex As Integer
    Dim targetCol As Variant
    Dim foundCol As Range
    
    ' Define your desired column order here (adjust headings to match your data)
    targetColumns = Array("Employee Number", "Status", "Full Name", "Department", "Hire Date")
    
    ' Choose which sheets to target:
    ' Option 1: Target specific sheets (uncomment and edit the sheet names)
    ' For Each ws In ActiveWorkbook.Sheets(Array("mySheet", "TeamData", "HRRecords"))
    
    ' Option 2: Target ALL sheets in the workbook (uncomment this line)
    For Each ws In ActiveWorkbook.Sheets
        ws.Activate
        With ws
            ' Loop through our target columns in the order we want them
            For colIndex = LBound(targetColumns) To UBound(targetColumns)
                targetCol = targetColumns(colIndex)
                
                ' Find the column with the matching heading
                Set foundCol = .Rows(1).Find(What:=targetCol, LookIn:=xlValues, LookAt:=xlWhole)
                
                If Not foundCol Is Nothing Then
                    ' Move the column to its correct position (colIndex +1 because columns start at 1)
                    foundCol.EntireColumn.Cut
                    .Columns(colIndex + 1).Insert Shift:=xlToRight
                Else
                    ' Optional: Alert if a target column is missing from the sheet
                    MsgBox "Column '" & targetCol & "' not found in sheet '" & ws.Name & "'", vbExclamation
                End If
            Next colIndex
        End With
    Next ws
    
    MsgBox "Column reordering finished!", vbInformation
End Sub

How to Use This: #

1. Set your target order: Edit the targetColumns array to include your exact column headings, in the order you want them to appear.
2. Pick your target sheets:
   • Use Option 1 to specify a list of sheet names (perfect for focusing on specific tabs).
   • Use Option 2 to apply the change to every sheet in the workbook.
3. Run the macro: It will loop through each sheet, locate each target column, and move it to the correct position.

Key Details: #

• The Find method uses xlWhole to match exact headings (avoids accidental partial matches).
• Using Cut + Insert preserves all cell formatting and data, unlike some reordering methods that can break formulas.
• The optional alert lets you know if a target column is missing from a sheet, so you can adjust your headings or sheet data as needed.

内容的提问来源于stack exchange，提问作者TurboCoder