CODEWITHSUNDEEP
vbaexcel
Vivek Udhayakumar
Vivek Udhayakumar

Reputation: 19

Issues while copy/pasting values from Excel using VBA

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

Upvotes: 1

Views: 109

Answers (1)

Paul Ogilvie
Paul Ogilvie

Reputation: 25266

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

Upvotes: 1

Related Questions