• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Expert

      VBA copy data from one worksheet named “PreviousFN” to another named”CurrentFN”

      Hello,

      I have two columns in a worksheet one with “EmpID”, and the other “Tax Paid”. I get reports every fortnight. Some EmpID gets added and some are dropped. I have worksheet “PreviousFN”

      I want to copy “Tax Paid” for the common “EmpID” from the “PreviousFN” to “CurrentFN”.

      How do I do it, please?

       

      Thanks

      Asked by raghuprabhu on April 8, 2017 in VBA.
      3 Answers
      Keymaster

      Hi, This Macro assumes you have EmpID, Tax Paid in Column A and B. And Data starts from the 2nd Row:

       

      Sub VBAF1_UpdateData()
      'LastRow in PreviousFN
      lastRowPrev = Sheets("PreviousFN").Cells(Sheets("PreviousFN").Rows.Count, "A").End(xlUp).Row
      'LastRow in CurrentFN
      lastRowCur = Sheets("CurrentFN").Cells(Sheets("CurrentFN").Rows.Count, "A").End(xlUp).Row
      'Now we will loop through each row in PreviousFN and serach for that EmpId in CurrentFN, and update the record if found.Other wise add at the last row
      For iCntrPrev = 2 To lastRowPrev
      blnExitst = 0
      For iCntrCur = 2 To lastRowCur
      'Check if EMPIds are macthing
      If Sheets("CurrentFN").Cells(iCntrCur, 1) = Sheets("PreviousFN").Cells(iCntrPrev, 1) Then
      'if matches then get the value from 2nd ccolumn of PreviousFN and place it at 2nd ccolumn of CurrentFN
      Sheets("CurrentFN").Cells(iCntrCur, 2) = Sheets("PreviousFN").Cells(iCntrPrev, 2)
      blnExitst = 1
      Exit For
      End If
      Next iCntrCur
      'if no match found then add new record
      If blnExitst = 0 Then
      lastRowCur = lastRowCur + 1
      Sheets("CurrentFN").Cells(lastRowCur, 1) = Sheets("PreviousFN").Cells(iCntrPrev, 1)
      Sheets("CurrentFN").Cells(lastRowCur, 2) = Sheets("PreviousFN").Cells(iCntrPrev, 2)
      End If
      Next iCntrPrev
      End Sub
      

      Thanks!

       

      Answered by PNRao on April 9, 2017..
      Expert

      Thanks a lot …fantastic!

       

      Answered by raghuprabhu on April 9, 2017..
      Expert

      Now I have added two more worksheets called “Omitted” and “Changed”.

       

      How do I change the above code? So that, if EMPIds are macthing copy them  “Changed”.   And if EMPIds are NOT macthing copy them to “Omitted” please?

       

      Thanks.

      Answered by raghuprabhu on April 23, 2017..