Indirect function is making excel file very slow.

[MandeepBajimaya]

I have an excel file which has 200 sheets for inventory items. These are for multiple companies on same sheet . I have to prepare summary for these items in the first sheet. Since I have used a lot of indirect function, it is making file very slow. What can I do to make it faster?

 

[MandeepBajimaya]

Thank you very much for your response sir. But even after adding both the codes, I am still not able to use the first code of Setformulas. I added it to module and it is shown in Macros list. But it doesn't run or show any changes.

Probably the easiest way to use it is to add a module. Right click on the project and select Insert > Module. It will be called Module1. Then add this code

Public Sub RefreshIndex()

   Worksheets("Index").SetFormulas

End Sub

From the Developer tab select Macros, click on RefreshIndex from the list, and click Run.

 

[6StringJazzer]

Very difficult to move forward from here without access to your file. If I can find time I will prepare a simplified mock-up and share it with you. Then you can compare my solution to how you have it implemented in your file and see if there is any difference.

 

[6StringJazzer]

Here is a mock-up with exactly the same code and the first few rows and columns of your data. It works fine.

MandeepBajimaya=update formulas.xlsm

Shared with Dropbox

www.dropbox.com

 

[MandeepBajimaya]

Here is a mock-up with exactly the same code and the first few rows and columns of your data. It works fine.

MandeepBajimaya=update formulas.xlsm

Shared with Dropbox

www.dropbox.com

Wow, got this. Thank you so much