技术需求：从Excel导入的table_2向table_1首列序号后新增行

Got it, let's walk through how to add rows to table_1's [NO] column using data pulled from table_2, right after the last existing row in table_1. I'll cover three practical approaches depending on your workflow preference:

1. Excel Formula Method (No Coding Required) #

If you want a quick, manual (but scalable) way:

• First, identify the last filled row in table_1's [NO] column. For structured Excel tables (List Objects), just click below the last row in the [NO] column—Excel will auto-expand the table for you.
• To dynamically pull data from table_2, use this formula in the first empty cell of table_1's [NO] column:

  =INDEX(Table2[NO],ROW()-COUNTA(Table1[NO]))
  

  Drag the fill handle down to pull all corresponding values from table_2. This formula automatically adjusts as you add more rows to either table.

2. VBA for Automated Bulk Insert #

For repetitive or large datasets, VBA saves you time. Here's a reusable macro:

Sub AppendNOFromTable2ToTable1()
    Dim tbl1 As ListObject, tbl2 As ListObject
    Dim appendStartRow As Long, i As Long
    
    ' Update sheet and table names to match your workbook
    Set tbl1 = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    Set tbl2 = ThisWorkbook.Worksheets("Sheet2").ListObjects("Table2")
    
    ' Calculate the first empty row in table_1's [NO] column
    appendStartRow = tbl1.ListRows.Count + 1
    
    ' Loop through table_2's [NO] values and append to table_1
    For i = 1 To tbl2.ListRows.Count
        ' Add a new row to table_1
        tbl1.ListRows.Add (appendStartRow + i - 1)
        ' Populate the [NO] column with table_2's value
        tbl1.ListColumns("NO").DataBodyRange(appendStartRow + i - 1) = tbl2.ListColumns("NO").DataBodyRange(i)
    Next i
End Sub

To use this:

• Press Alt + F11 to open the VBA editor
• Insert a new module, paste the code
• Adjust the sheet/table names to match your file
• Run the macro

3. Power Query (Get & Transform) for Repeatable Updates #

If you need to refresh this data regularly, Power Query is your best bet:

1. Go to the Data tab, load both table_1 and table_2 into Power Query (click From Table/Range for each)
2. In the Power Query Editor for table_1, click Append Queries > Append Queries as New
3. Select Table2 as the table to append, then click OK
4. Remove any columns you don't need (keep only [NO])
5. Click Close & Load to export the combined table back to Excel
6. Whenever table_2 updates, right-click the combined table and select Refresh to pull in new data

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