Issues while copy/pasting values from Excel using VBA

Question

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.

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

Are you say that the paste works without error, but the values pasted are incorrect?? — Gary's Student
– Gary's Student, Commented Jun 20, 2015 at 11:50
I have modified the code and now its working fine... But when I run the macro in Excel 2007 its getting completed within 30 seconds, whereas in Office 365 proplus its running for more than half an hour and not getting completed at all.. here is the modified code.. can you please check and advise. — Vivek Udhayakumar
– Vivek Udhayakumar, Commented Jun 20, 2015 at 18:11

Paul Ogilvie · Accepted Answer · 2015-06-20 12:09:17Z

1

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

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

answered Jun 20, 2015 at 12:09

Paul Ogilvie

25.3k4 gold badges25 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issues while copy/pasting values from Excel using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related