Conditional copy Excel File-2 data to excel file-1?

Question

I am using Excel 2007. I try to copy Unit-price from the Excel file-2 data to the Excel file-1 when certain columns data matching from file-1 with file-2.

Thanks for the helps & guidance.

My VBA Code:

Sub mySales() 
    Dim LastRow As Integer, i As Integer, erow As Integer, Pipe_Class As String, Pipe_Description As String, End_Type As String, Pipe_Size As String
    Dim wbk As Workbook
    strPriceFile = "C:\Temp\File-2.xlsx"
    LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        Pipe_Class = ""
        Pipe_Description = ""
        End_Type = ""
        Pipe_Size = ""
        Pipe_Class = ActiveSheet.Cells(i, 1).Value
        Pipe_Description = ActiveSheet.Cells(i, 2).Value
        End_Type = ActiveSheet.Cells(i, 3).Value
        Pipe_Size = ActiveSheet.Cells(i, 4).Value
        Set wbk = Workbooks.Open(strPriceFile)
        Worksheets("SOR2").Select
        If Cells(i, 1) = Pipe_Class And Cells(i, 2) = Pipe_Description And Cells(i, 3) = End_Type And Cells(i, 4) = Pipe_Size Then
            Range(Cells(i, 12), Cells(i, 12)).Select
            Selection.Copy

??? After Here how select my current file & paste ????????

            Worksheets("SOR1").Select
            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            ActiveSheet.Cells(erow, 12).Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
        End If
    Next i
    ActiveWorkbook.Close
    Application.CutCopyMode = False
End Sub

And the question is .... ? (P.S. If your problem is that LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row crashes out, change “ and ” to ".) — YowE3K
P.P.S. If your problem is that you aren't able to open strSecondFile because it is already open, move the open statement outside the loop. — YowE3K
STILL I AM NOT SUCCEEDED. DOES ANY BODY CAN HELP ME ? THANKS — Paramu
Can you please let us know what the problem is? Without a description of the error message, and which line is causing problems, we are just having to guess at what is wrong. — YowE3K
Hi YowE3K, Thanks for the reply....Now I edit my post, Confusion with paste area....Pls. check the post again....Thanks — Paramu

YowE3K YowE3K · Accepted Answer · 2016-12-22T09:34:01

I haven't checked all your code, but I have refactored what you have in your question in an attempt to open the Workbook once and to assign proper objects so that you can keep track of what action is being applied to which worksheet.

Sub mySales() 
    Dim LastRow As Integer, i As Integer, erow As Integer
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim wbDst As Workbook
    Dim wsDst As Worksheet
    Dim strPriceFile As String

    Set wbDst = ActiveWorkbook
    Set wsDst = ActiveSheet

    strPriceFile = "C:\Temp\File-2.xlsx"
    Set wbSrc = Workbooks.Open(strPriceFile)
    Set wsSrc = wbSrc.Worksheets("SOR2")

    LastRow = wsDst.Range("A" & wsDst.Rows.Count).End(xlUp).Row
    erow = LastRow + 1

    For i = 2 To LastRow
        If wsSrc.Cells(i, 1).Value = wsDst.Cells(i, 1).Value And _
           wsSrc.Cells(i, 2).Value = wsDst.Cells(i, 2).Value And _
           wsSrc.Cells(i, 3).Value = wsDst.Cells(i, 3).Value And _
           wsSrc.Cells(i, 4).Value = wsDst.Cells(i, 4).Value Then

            wsSrc.Cells(i, 12).Copy wsDst.Cells(erow, 12)
            erow = erow + 1  ' your current code would always copies to the same row,
                             ' but I **think** you probably want to copy to the
                             ' next row each time
        End If
    Next i

    wbSrc.Close
    If erow > LastRow + 1 Then
       wbDst.Save
    End If
    wbDst.Close
End Sub

The code is completely untested but, even if it doesn't work, at least it should give you an idea of how you should be processing multiple workbooks and multiple worksheets.

Conditional copy Excel File-2 data to excel file-1?

1 Answers