如何批量对所有工作表执行列隐藏操作？

Hey Todd, huge props for sticking with it and getting that single-sheet column hiding working after 12 hours—seriously, that’s the kind of persistence that pays off! 🎉 Since you already have the core logic down, expanding it to all your daily worksheets is just a matter of adding a loop to iterate through every sheet in your file. Let’s break this down with concrete examples depending on which tool you’re using:

The core idea is simple: take the column-hiding code you already wrote, wrap it in a loop that goes through every worksheet, and run it on each sheet one by one.

Option 1: Excel VBA (Most Common for Desktop Excel) #

Step 1: Turn Your Single-Sheet Code into a Reusable Function #

First, refactor your existing code to accept a worksheet as input—this makes it easy to reuse across multiple sheets. If your original code looked like this:

Sub HideColumnsOnSingleSheet()
    ' Your existing column-hiding logic here
    Sheet1.Columns("C:F").Hidden = True
End Sub

Rewrite it to:

Sub HideColumnsOnSheet(targetSheet As Worksheet)
    ' Replace this line with your actual column-hiding logic
    targetSheet.Columns("C:F").Hidden = True
End Sub

Step 2: Add a Loop to Cover All Sheets #

Create a main subroutine that loops through every worksheet in your workbook and calls the reusable function:

Sub HideColumnsOnAllSheets()
    Dim ws As Worksheet
    
    ' Loop through every sheet in the workbook with your code
    For Each ws In ThisWorkbook.Worksheets
        HideColumnsOnSheet ws
    Next ws
End Sub

Quick Customization Tip:

If you need to skip a specific sheet (like a weekly summary sheet), add a condition inside the loop:

For Each ws In ThisWorkbook.Worksheets
    ' Skip the sheet named "Weekly Summary"
    If ws.Name <> "Weekly Summary" Then
        HideColumnsOnSheet ws
    End If
Next ws

Option 2: Google Sheets Apps Script #

If you’re working in Google Sheets, the approach is nearly identical—just use JavaScript to loop through sheets:

function hideColumnsOnAllSheets() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = spreadsheet.getSheets();
  
  // Loop through each sheet and apply your column-hiding logic
  allSheets.forEach(sheet => {
    // Replace with your logic (example: hide columns 3-6, which is C:F)
    sheet.hideColumns(3, 4); // First number = start column, second = number of columns to hide
  });
}

Final Step: Test It Out #

Run the main subroutine/function, then spot-check a few of your daily worksheets to confirm the column hiding works exactly like it did on your single sheet. Since you already validated the core logic, this should just replicate that behavior across all your sheets.

If you need help tweaking this to match your specific column-hiding rules, just share a snippet of your original code and we can refine it together!

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