VBA宏开发需求：将Raw Data匹配表名的行复制至对应站点工作表

Hey there! I get that specifying ranges in VBA can feel tricky when you're starting out—let's walk through building this macro step by step, with clear explanations for all the range-related bits that might be tripping you up.

Step 1: Lay Out the Core Logic #

Here's what we need the macro to do, broken down into simple chunks:

• Loop through every row with data in column A of your Raw Data sheet
• Check if the value in column A matches the name of any other worksheet in the workbook
• If it does, copy columns A-H of that row to the next empty row in the matching site worksheet

Step 2: Full Macro Code with Annotations #

I’ve added detailed comments to explain each part, especially the range specifications you’re confused about:

Sub CopyDataToSiteSheets()
    Dim wsRaw As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRowRaw As Long
    Dim i As Long
    Dim nextRowTarget As Long
    
    ' Turn off screen updates to speed up the macro (optional but recommended)
    Application.ScreenUpdating = False
    
    ' Set a reference to your Raw Data worksheet (avoids bugs from active sheet switches)
    Set wsRaw = ThisWorkbook.Worksheets("Raw Data")
    
    ' Find the last row with data in column A of Raw Data (no hardcoding rows!)
    lastRowRaw = wsRaw.Cells(wsRaw.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row starting from row 2 (assuming row 1 is headers)
    For i = 2 To lastRowRaw
        ' Try to reference the worksheet named in column A of the current row
        On Error Resume Next ' Temporarily ignore errors if the sheet doesn't exist
        Set wsTarget = ThisWorkbook.Worksheets(wsRaw.Cells(i, "A").Value)
        On Error GoTo 0 ' Re-enable normal error handling
        
        ' If the target worksheet exists...
        If Not wsTarget Is Nothing Then
            ' Find the next empty row in the target sheet's column A
            nextRowTarget = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row + 1
            
            ' Copy columns A-H from Raw Data to the target sheet's empty row
            wsRaw.Range(wsRaw.Cells(i, "A"), wsRaw.Cells(i, "H")).Copy _
                Destination:=wsTarget.Cells(nextRowTarget, "A")
            
            ' Clear the target sheet reference for the next loop iteration
            Set wsTarget = Nothing
        End If
    Next i
    
    ' Turn screen updates back on
    Application.ScreenUpdating = True
    
    ' Notify you when the macro finishes
    MsgBox "Data copied successfully!", vbInformation
End Sub

Key Range Specification Breakdown #

Let’s demystify the range parts that might have been confusing:

• Finding the last row in Raw Data: wsRaw.Cells(wsRaw.Rows.Count, "A").End(xlUp).Row
  • This starts at the very bottom of column A (row 1048576 in modern Excel) and moves up until it hits the first cell with data. No more guessing how many rows your dataset has!
• Selecting columns A-H for a single row: wsRaw.Range(wsRaw.Cells(i, "A"), wsRaw.Cells(i, "H"))
  • We use wsRaw.Cells to explicitly reference the Raw Data sheet, even if another sheet is active. This prevents accidental bugs from sheet switches.
• Finding the next empty row in the target sheet: wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row + 1
  • Similar to the last row trick, but we add 1 to get the first empty row below existing data. This ensures we never overwrite existing content in the site sheets.

Extra Tips for Your Workflow #

• If your Raw Data sheet doesn’t have headers, start the loop at row 1 instead of row 2.
• If you want to clear existing data in site sheets before copying, add this line right after setting wsTarget:

  wsTarget.Range("A2:H" & wsTarget.Rows.Count).ClearContents
  

• Always test the macro on a copy of your workbook first—better safe than sorry!

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