如何实现将Excel行数据按指定规则迁移至预定义工作表？

Hey there! Handling daily large Excel files (15k rows, 22 columns) and sorting rows by Column A values into specific sheets is totally doable—let’s break down the best approaches based on how automated you want this to be.

If you only need to do this occasionally, the built-in filter tool works fine:

• Open your source worksheet, select Column A, then go to the Data tab and click Filter.
• Click the dropdown arrow in Column A’s header, check the target value (e.g., "Home"), and hit OK to show only matching rows.
• Select all visible rows (press Ctrl+A to select the entire dataset, then Ctrl+C to copy).
• Switch to Sheet2, select cell A1, and press Ctrl+V to paste. Go back to the source sheet and turn off the filter.
• Repeat this process for other values like "Work" into Sheet3.

Power Query is perfect for repeat tasks—it lets you save your workflow and refresh it with one click when you get the new daily file:

1. Import Data into Power Query:
   • Go to the Data tab, click From Table/Range (make sure your data has a header row; if not, check "My table has headers" in the popup).
2. Filter & Export for Each Category:
   • In the Power Query Editor, select Column A, go to Home > Keep Rows > Keep Rows Where, set the condition to "equals" your target value (e.g., "Home").
   • Click Close & Load To, choose Existing Worksheet, select Sheet2’s A1 cell, and hit OK.
   • Repeat this process for "Work" (filter Column A to "Work", close & load to Sheet3).
3. Daily Refresh:
   • When you get the new daily file, replace the source data with the new content, then go to the Data tab and click Refresh All—all your target sheets will update automatically.

If you want a one-click solution to handle everything automatically (even creating new sheets for unknown Column A values), use a VBA macro:

1. Open the VBA Editor: Press Alt+F11 in Excel.
2. Create a New Module: Right-click your workbook name in the Project Explorer, select Insert > Module.
3. Paste This Code:

Sub SplitDataByColumnA()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim category As String
    
    ' Set your source worksheet (update "Sheet1" to your actual source sheet name)
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row (start at row 2 assuming row 1 is the header)
    For i = 2 To lastRow
        category = wsSource.Cells(i, "A").Value
        
        ' Handle invalid worksheet name characters (replace with "-")
        category = Replace(Replace(Replace(Replace(category, "/", "-"), "\", "-"), ":", "-"), "?", "-")
        
        ' Check if target sheet exists; create it if not
        On Error Resume Next
        Set wsTarget = ThisWorkbook.Sheets(category)
        On Error GoTo 0
        
        If wsTarget Is Nothing Then
            Set wsTarget = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            wsTarget.Name = category
            ' Copy header row to new sheet
            wsSource.Rows(1).Copy Destination:=wsTarget.Rows(1)
        End If
        
        ' Copy current row to the next empty row in target sheet
        wsSource.Rows(i).Copy Destination:=wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Offset(1, 0)
        
        ' Reset target sheet variable
        Set wsTarget = Nothing
    Next i
    
    MsgBox "Data split completed successfully!"
End Sub

4. Customize & Run:
   • Update wsSource = ThisWorkbook.Sheets("Sheet1") to match your source sheet’s name.
   • If your header isn’t in row 1, change For i = 2 To lastRow to start at the correct row.
   • Press F5 to run the macro, or go back to Excel, open the Developer tab, click Macros, select SplitDataByColumnA, and hit Run.

Quick Notes for Large Files: #

• VBA might take 5-10 seconds to process 15k rows—be patient, it’s way faster than manual work.
• Always back up your source file before running macros, just in case.
• If Column A has duplicate values, the macro will append all matching rows to the target sheet correctly.

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