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

Answered

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

Top Contributor Asked on April 8, 2017 in VBA.
Add Comment
3 Answer(s)
Best answer

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!

 

Keymaster Answered on April 9, 2017.
Add Comment

Thanks a lot …fantastic!

 

Top Contributor Answered on April 9, 2017.
Add Comment

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.

Top Contributor Answered on April 23, 2017.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.