Excel VBA复制列至另一工作簿报错：需从A2粘贴（A1为表头）

Let's break down why your code is throwing that error and adjust it to paste starting at cell A2 as you intended.

What's Causing the Error? #

The issue stems from this line:

y.Sheets("data").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues

If your data sheet only has a header in A1 (with no data below it), Range("A1").End(xlDown) will jump straight to the last row of the worksheet (A1048576). Trying to offset one row below that is impossible—Excel doesn't have a row 1048577, hence the error.

Corrected Code #

Here's the revised version that reliably pastes starting at A2 (or the next empty row if there's existing data below the header):

Sub CopyColumnToAnotherWorkbook()
    ' Define and set your source and target workbooks (adjust names as needed)
    Dim x As Workbook
    Dim y As Workbook
    
    ' Example: Replace "SourceFile.xlsx" with your actual source workbook name
    Set x = Workbooks("SourceFile.xlsx")
    ' Use ThisWorkbook if the target is the workbook containing this macro
    Set y = ThisWorkbook
    
    ' Clear existing data from A2 to the end of column A in target sheet
    y.Sheets("data").Range("A2:A" & y.Sheets("data").Rows.Count).Clear
    
    ' Find the first empty row in column A of target sheet (starts at A2 if header is only A1)
    Dim targetStartRow As Long
    targetStartRow = y.Sheets("data").Cells(y.Sheets("data").Rows.Count, "A").End(xlUp).Row + 1
    
    ' Copy values from source column C to target starting at the calculated row
    x.Sheets("file").Range("C:C").Copy
    y.Sheets("data").Cells(targetStartRow, "A").PasteSpecial xlPasteValues
    
    ' Clear the clipboard to remove the "marching ants" selection
    Application.CutCopyMode = False
End Sub

Key Improvements: #

• Reliable Last Row Detection: Using Cells(Rows.Count, "A").End(xlUp).Row finds the last occupied row in column A, so adding 1 gives us the first empty row. If only A1 has data, this will be row 2—exactly what you need.
• Explicit Workbook References: We explicitly define and set x and y to avoid ambiguity (make sure to replace "SourceFile.xlsx" with your actual source workbook name).
• Cleaner Range Clearing: We clear from A2 to the last row of the sheet instead of hardcoding A1048576 (works for both Excel 365 and older versions).
• Clipboard Cleanup: Application.CutCopyMode = False removes the copy selection highlight after the operation.

Quick Notes: #

• Make sure both workbooks are open when running the macro, or adjust the code to open the source workbook if needed.
• Double-check that the sheet names ("data" and "file") match exactly—sheet names are case-insensitive but must be spelled correctly.

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