What I have done is, I have created a macro in excel first. Then by editing that macro, I have extracted VB code. And this VB code I have translated into perl using WIN32::OLE.

Sounds like you've got the right idea. One commentary note. Personally, I like to skip translation on the select and activate stuff. Just seems like unnecessary extra steps to me.

     I have three different formulas to be pasted in specific column in three different worksheets. I am doing it one by one in loop. First I am pasting a formula in a cell. Then I am copying that cell & pasting it in cells below.

So my question here is why do you feel the need to 'copy and paste' with this task? If you have the formula in a variable, you should be able to just insert that into all 3 cells. The only advantage of 'cut and paste' is that Excel will update the formula for cells specified as 'B1' format in the formula while leaving '$B$1' formatted cell references as is.

     $Sheet->Paste

I think that this line may be the culprit. You copied a formula from a cell and trying to paste it into a worksheet. Instead, you need to paste into a cell.

     I am sorry about the limited code sharing due to restrictions I have to abide by.

There's a lot of good reason for not sharing all of your code. However, if you can try to provide some code that similar enough to your code that someone can run it and reproduce the errors/problems, you'll get much better help.