Excel VBA PasteSpecial xlPasteValues 语法错误

Question

我正在尝试将数据从一个 .xlsx 文件复制到我的 .xlsm 文件，但没有格式化。当我尝试使用以下代码行时，会出现未指定的语法错误。

TargetSheet.Range("A"& TargetLastRow).PasteSpecial xlPasteValues(或其他各种形式)

Sub Import_Click()

'Set the target workbook for import.'
Dim TargetWorkbook As Workbook
Set TargetWorkbook = Application.ActiveWorkbook

'Prompt window for user to select and import file.'
Dim UserFilename As String
UserFilename = Application.GetOpenFilename

Dim UserWorkbook As Workbook
Set UserWorkbook = Application.Workbooks.Open(UserFilename)

'Copy data from source to target sheet.'
Dim SourceSheet As Worksheet
Set SourceSheet = UserWorkbook.Worksheets(1)

Dim TargetSheet As Worksheet
Set TargetSheet = TargetWorkbook.Worksheets(1)

Dim SourceLastRow As Long
SourceLastRow = SourceSheet.Cells(SourceSheet.Rows.Count, "A").End(xlUp).Row

Dim TargetLastRow As Long
TargetLastRow = TargetSheet.Cells(TargetSheet.Rows.Count, "A").End(xlUp).Offset(1).Row

SourceSheet.Range("A2:S" & SourceLastRow).Copy _
TargetSheet.Range("A" & TargetLastRow)

'Close import workbook.'
UserWorkbook.Close

End Sub

Answer 1

您可以通过将目的地作为.Copy 的第一个参数来复制一行。

SourceSheet.Range("A2:S" & SourceLastRow).Copy Destination:=TargetSheet.Range("A" & TargetLastRow)

或者，如果您想使用 SpecialPaste，您需要在 2 个单独的语句中执行此操作:

SourceSheet.Range("A2:S" & SourceLastRow).Copy 'no destination given here
'separate statement for pasting:
TargetSheet.Range("A" & TargetLastRow).PasteSpecial xlPasteValues

注意不能有 _作为 .Copy 末尾的行连接符那就行!