2

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
Improve this question
5
  • 1
    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 ".) Commented Dec 22, 2016 at 2:36
  • 1
    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. Commented Dec 22, 2016 at 2:38
  • STILL I AM NOT SUCCEEDED. DOES ANY BODY CAN HELP ME ? THANKS Commented Dec 22, 2016 at 3:35
  • 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. Commented Dec 22, 2016 at 3:38
  • Hi YowE3K, Thanks for the reply....Now I edit my post, Confusion with paste area....Pls. check the post again....Thanks Commented Dec 22, 2016 at 7:31

1 Answer 1

Reset to default
4

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.

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.