在使用VBA从Excel复制/粘贴值时出现问题

I am trying to read a source Excel file and copying some of the values and assigning the same to different cells in the target sheet. But after execution of the macro. the value pasted are not as expected.

我正在尝试读取一个源Excel文件并复制一些值并将它们分配给目标表中的不同单元。但是在执行宏之后。粘贴的值不像预期的那样。

Code:

代码:

Sub Import()
Dim SourceFile As Workbook
Dim SourceTab As Worksheet
Dim TargetTab As Worksheet

SourceFileName = Application.GetOpenFilename("Excel Files , *.xls;*.xlsx;*.csv")

If SourceFileName = False Then Exit Sub
Application.ScreenUpdating = False

Set TargetTab = Sheets("Output")
'TargetRow = TargetTab.Cells(TargetTab.Cells.Rows.Count, 1).End(xlUp).Row + 1
TargetRow = 2
Set SourceFile = Workbooks.Open(SourceFileName)

SourceFile.Activate
Set SourceTab = Sheets("Input")
SourceTab.Activate

For i = 1 To Cells(Cells.Rows.Count, 2).End(xlUp).Row

    If SourceTab.Cells(i, 2) = "VS" Then
        TargetTab.Cells(i, 3).Value = SourceTab.Cells(i, 31).Value
        TargetTab.Cells(i, 5).Value = SourceTab.Cells(i, 11).Value
        TargetTab.Cells(i, 6).Value = SourceTab.Cells(i, 19).Value
        TargetTab.Cells(i, 7).Value = SourceTab.Cells(i, 27).Value
        TargetTab.Cells(i, 5).Value = SourceTab.Cells(i, 4).Value
        TargetTab.Cells(i, 11).Value = SourceTab.Cells(4, 5).Value
        TargetTab.Cells(i, 13).Value = SourceTab.Cells(2, 25).Value
        TargetTab.Cells(i, 16).Value = SourceTab.Cells(i, 8).Value

        SourceTab.Cells(i, 3).Resize(1, 50).Copy
        ThisWorkbook.Activate
        TargetTab.Activate
        Cells(TargetRow, 2).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        SourceFile.Activate

        TargetRow = TargetRow + 1
        'TargetNewRows = TargetNewRows + 1
    End If

Next
SourceFile.Close False
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

1 个解决方案

#1

As Gary's comment indicates, your problem is not very clear, however, I'll give it a shot.

正如Gary的评论所指出的，你的问题不是很清楚，但是，我还是试一试。

Note that in:

注意:

For i = 1 To Cells(Cells.Rows.Count, 2).End(xlUp).Row

the Cells(... expression is evaluated every iteration of the loop. If the selection changes during the loop, then it can give a different result every iteration. I suggest you do something like:

细胞(…表达式在循环的每次迭代中进行计算。如果在循环中选择更改，那么它可以在每次迭代中给出不同的结果。我建议你做以下事情:

j = Cells(Cells.Rows.Count, 2).End(xlUp).Row
For i = 1 To j

#1