基于VBA实现Sheet1数据转Sheet2的ERP导入格式表格需求

Step 1: Add a Button to Sheet2 #

• Enable the Developer tab if it’s hidden: Go to File > Options > Customize Ribbon, check the box for "Developer".
• Click Insert under the Developer tab, select Button (Form Control).
• Draw the button on Sheet2, then pick New in the "Assign Macro" window to open the VBA editor.

Step 2: Paste the VBA Code #

Replace the default empty code with this:

Sub ImportDataFromSheet1()
    Dim srcTable As ListObject
    Dim destSheet As Worksheet
    Dim i As Long
    Dim destStartRow As Integer
    
    ' Set references to your source table and destination sheet
    ' Replace "PQ_Table" with your actual Power Query table name if you named it
    Set srcTable = ThisWorkbook.Sheets("Sheet1").ListObjects("PQ_Table")
    ' If you didn't name the table, use ListObjects(1) (assumes it's the first table on Sheet1)
    ' Set srcTable = ThisWorkbook.Sheets("Sheet1").ListObjects(1)
    
    Set destSheet = ThisWorkbook.Sheets("Sheet2")
    
    ' Clear old data in Sheet2 (adjust destStartRow if you have headers)
    destStartRow = 2 ' Use 1 if there are no headers in Sheet2
    If destSheet.Cells(destStartRow, 1).Value <> "" Then
        destSheet.Range(destSheet.Cells(destStartRow, 1), _
                        destSheet.Cells(destSheet.Cells(Rows.Count, 1).End(xlUp).Row, Columns.Count)).ClearContents
    End If
    
    ' Loop through each row in the source table
    For i = 1 To srcTable.ListRows.Count
        ' Write iterative row number (column 1)
        destSheet.Cells(destStartRow + i - 1, 1).Value = i
        
        ' Copy all source table data to Sheet2 (starts at column 2)
        srcTable.ListRows(i).Range.Copy destSheet.Cells(destStartRow + i - 1, 2)
        
        ' Write fixed value columns
        Dim lastDataCol As Integer
        lastDataCol = 2 + srcTable.ListColumns.Count
        destSheet.Cells(destStartRow + i - 1, lastDataCol).Value = 1.01 ' min_qty
        destSheet.Cells(destStartRow + i - 1, lastDataCol + 1).Value = "yes" ' next row
    Next i
    
    ' Optional: Auto-fit columns for readability
    destSheet.UsedRange.Columns.AutoFit
    
    MsgBox "Data imported successfully!", vbInformation
End Sub

Step 3: Key Code Explanations (For Beginners) #

• Source Table Reference: ListObject is Excel’s official object for structured tables (like those from Power Query). Using it ensures we only process actual table rows, no empty rows.
• Clear Old Data: We target rows starting from destStartRow (adjust to 1 if no headers) down to the last used row, so we don’t erase headers or other static content.
• Iterative Row Number: The loop variable i acts as the row number, written to the first column of Sheet2.
• Fixed Values: These are added right after the source data columns, using srcTable.ListColumns.Count to dynamically find the correct column position (works even if you add/remove columns in the source table later).

Step 4: Test the Code #

• Go back to Sheet2 and click the button. A pop-up will confirm success if everything works.
• Troubleshooting tips:
  • Double-check sheet names ("Sheet1" and "Sheet2" must match your workbook).
  • Ensure your Power Query data is formatted as an Excel Table (not just a regular range).
  • If you get an error with ListObjects("PQ_Table"), use ListObjects(1) instead (it targets the first table on Sheet1).

Best Practices to Remember #

• Always use ListObject for Power Query tables—it handles dynamic row/column changes automatically.
• Avoid hardcoding column positions; use ListColumns.Count to keep the code flexible.
• Clear only the data range, not the entire sheet, to preserve any static content like headers.

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