I do not know why this does this. But I was able to work around the problem. Please try this...
Maybe try declaring the FSO as Object. "Dim FSO" without a declaration declares the FSO as a variant. Just a...
Each block is a named range. There are 6 Key colors which are based on the information in column “Y”(Rental...
I cleaned up the code. This will place next month and year in cell A1. So today is Sep30 2018...
This was tested in a blank worksheet (no data). The macro will place next Month and Year in cell A1....
Updated the code to include the possibility of blank cells in the raw data. If a blank cell is found...
Lets try this again. You will need 3 sheets. Sheet1, Sheet 2 and Sheet3 Sheet1 your raw data Sheet2 is...
Hey Thank you so much. Apologies for not been very clear on the question. Using your example, in the output...
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?
Hi, This Macro assumes you have EmpID, Tax Paid in Column A and B. And Data starts from the 2nd Row:
'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
'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)
Thanks a lot …fantastic!
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?
EMPIds are macthing
EMPIds are NOT macthing
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).