Ganesh, Good morning.
I don't know Excel VBA programming.
"...the values in the columns "Rate/kg & amount" is changed to 01.4, 0.00, 0.12 ..etc ..."
When your macro runs it is erasing part of the data of the two tabs: Buffalo and Cow.
So the search is all wrong.
The problem is in the Macro.
In tab Buffalo, column B line 18 to 53, the values have several decimal places.
As the cell is formatted to show only two decimal places you can see 6.50 but actually is 6.49999999999999.
This will make the search fails when you search this value.
I advise you to correct these numbers to prevent future problems.
"...Also i want to maintain a every day data in a separate excel sheet. Can you please give me an idea & solution. ..."
It may be helpful for you to have the spreadsheet with daily data in a file and another file with data separated by codes.
Thus, using VBA you can update them whenever necessary.
As we do not know the logistics of your business it is difficult to speak with precision the best solution.
Checking more carefully your spreadsheet, we have noticed that you use different formulas for each type of milk.
Do you enter the formulas manually for each type of milk found?
If the answer is YES, this is not necessary.
It is hard work and time spent typing.
Make Excel work for you.
The automation serves this purpose.
Besides being much faster and much more secure and professional.
Try to use:
Main Database --> N6
=IF(F6="BM",INDEX(Buffalo!$C$3:$X$53,MATCH(J6,Buffalo!$B$3:$B$53,1),MATCH(K6,Buffalo!$C$2:$X$2,1)),IF(F6="CM",INDEX(Cow!$C$3:$W$28,MATCH(J6,Cow!$B$3:$B$28,1),MATCH(K6,Cow!$C$2:$W$2,1)),"")) + MOD(K6,0.1)*2
Copy it down as necessary.
I hope it helps.
Have a nice weekend.
Belo Horizonte, Brasil.