VBA宏需求：遍历工作簿提取工作表名及指定单元格值

Hey there! Let's tweak your existing macro to handle both extracting sheet names and checking for valid IED addresses in cell H4 all in one run. The main issue with your previous attempt was likely not properly referencing the target workbook (instead relying on ActiveWorkbook, which can be unreliable) and missing clear logic to validate the H4 value range.

Here's the revised code that does exactly what you need:

Sub GetSheetnamesAndIEDAddresses()
    'Turn off alerts to avoid popups during workbook operations
    Application.DisplayAlerts = False
    
    'Declare variables for clearer, more reliable references
    Dim targetWB As Workbook
    Dim ws As Worksheet
    Dim outputWS As Worksheet
    Dim i As Integer
    
    'Open the target workbook and assign it to a variable (no more guesswork with ActiveWorkbook!)
    Set targetWB = Workbooks.Open(Filename:="D:\Projects\ASE Templates\ASE Template White Book.xlsx")
    'Set reference to your output worksheet
    Set outputWS = ThisWorkbook.Worksheets("Tab Names from white book")
    
    'Clear both A and B columns to remove old data
    outputWS.Range("A:B").ClearContents
    
    i = 0 'Reset counter
    'Loop through each worksheet in the target workbook
    For Each ws In targetWB.Worksheets
        i = i + 1
        'Write sheet name to column A
        outputWS.Range("A" & i).Value = ws.Name
        
        'Check if H4 contains a valid IED address (1-54)
        With ws.Range("H4")
            If IsNumeric(.Value) Then 'First confirm it's a number
                If .Value >= 1 And .Value <= 54 Then
                    'Valid address: write to column B
                    outputWS.Range("B" & i).Value = .Value
                Else
                    'Optional: write a note if value is outside range
                    'outputWS.Range("B" & i).Value = "Out of range"
                End If
            Else
                'Optional: write a note if H4 isn't a number
                'outputWS.Range("B" & i).Value = "Not a number"
            End If
        End With
    Next ws
    
    'Close the target workbook without saving (since we didn't modify it)
    targetWB.Close SaveChanges:=False
    'Turn alerts back on
    Application.DisplayAlerts = True
End Sub

Key Improvements & Explanations: #

• Target Workbook Variable: By assigning the opened workbook to targetWB, we avoid relying on ActiveWorkbook (which can shift unexpectedly if you click other windows). This makes the code far more reliable.
• Clear Both Columns: We now clear both A and B columns to ensure old data doesn't linger from previous runs.
• Validity Check for H4: The code first checks if H4 contains a numeric value, then verifies it falls between 1 and 54. Only valid values get written to column B; you can uncomment the optional lines if you want to add notes for invalid entries.
• Cleaner Structure: Using With blocks for the H4 range makes the code more concise and easier to read.

Why Your Previous Attempt Might Have Failed: #

If you tried modifying the original code without referencing the target workbook explicitly, you might have been pulling data from the wrong workbook (your active workbook instead of the "White Book" one). Using the targetWB variable eliminates this confusion entirely.

Give this macro a test run, and it should populate both columns exactly as you need!

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