如何将指定列数据按自定义顺序复制到另一电子表格？

Hey there! Let's break this down step by step since you're new to spreadsheet automation—your initial loop-based idea is totally on the right track, we just need to refine it to make it work smoothly. Below are practical, actionable solutions for both Excel and Google Sheets, plus a no-code alternative if you prefer that.

Option 1: Excel VBA (Automated, Great for Repeated Use) #

Your loop approach makes sense, but copying entire columns at once is faster and less error-prone than reading each cell individually. Here's a ready-to-use script with explanations:

Step 1: Open the VBA Editor #

• Open both your source and target spreadsheets
• Press Alt + F11 to launch the editor
• Right-click your workbook name in the left pane → Insert → Module

Step 2: Paste & Customize the Code #

Drop this into the module, then tweak the labeled parts to match your actual sheet names and column mappings:

Sub CopyCustomColumns()
    ' Define source and target sheets
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Sheets("YourSourceSheetName") ' Replace with your source sheet name
    Set targetSheet = Workbooks("TargetFileName.xlsx").Sheets("YourTargetSheetName") ' Replace with target workbook/sheet name
    
    ' Map source columns to target columns (add more rows if you need extra columns)
    Dim columnMapping As Variant
    columnMapping = Array( _
        Array("A", "A"), ' Source A → Target A
        Array("C", "B"), ' Source C → Target B
        Array("D", "C"), ' Source D → Target C
        Array("F", "D")  ' Source F → Target D
    )
    
    ' Find the last row with data in the source sheet (avoids copying empty rows)
    Dim lastRow As Long
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row ' Use a column that always has data
    
    ' Loop through each column pair and copy data
    Dim i As Integer
    Dim sourceCol As String, targetCol As String
    For i = LBound(columnMapping) To UBound(columnMapping)
        sourceCol = columnMapping(i)(0)
        targetCol = columnMapping(i)(1)
        
        ' Copy the entire column range in one go
        sourceSheet.Range(sourceCol & "1:" & sourceCol & lastRow).Copy _
            Destination:=targetSheet.Range(targetCol & "1")
    Next i
    
    MsgBox "Data copied successfully!"
End Sub

Key Notes #

• The columnMapping array lets you easily add more columns—just add another Array("SourceCol", "TargetCol") line
• Using End(xlUp) automatically finds the last row with data, so you don't have to hardcode row numbers
• This is way faster than looping through every single cell, especially if you have large datasets

Option 2: Google Sheets Apps Script #

If you're using Google Sheets, here's an equivalent script:

function copyCustomColumns() {
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YourSourceSheetName");
  const targetSheet = SpreadsheetApp.openById("YourTargetSheetID").getSheetByName("YourTargetSheetName");
  
  // Map source column index (1 = A) to target column index
  const columnMapping = [
    [1, 1], // Source A → Target A
    [3, 2], // Source C → Target B
    [4, 3], // Source D → Target C
    [6, 4]  // Source F → Target D
  ];
  
  const lastRow = sourceSheet.getLastRow();
  
  columnMapping.forEach(mapping => {
    const sourceCol = mapping[0];
    const targetCol = mapping[1];
    const values = sourceSheet.getRange(1, sourceCol, lastRow, 1).getValues();
    targetSheet.getRange(1, targetCol, lastRow, 1).setValues(values);
  });
  
  SpreadsheetApp.getUi().alert("Data copied successfully!");
}

Option 3: No-Code Manual Mapping (For Small Datasets) #

If you don't want to use scripts, use the INDEX function to manually link columns:

• In your target sheet cell A1: =SourceSheet!A1
• Target cell B1: =SourceSheet!C1
• Target cell C1: =SourceSheet!D1
• Target cell D1: =SourceSheet!F1
• Then drag the fill handle down to copy the formula to all rows

This works great if you only need to do this once or twice.

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