Need Macros to replace the Formula. Please Help if you can.

  • hello good people
    i have attached my file here. currently i am pulling data in "File_1.xlsx" (file attached) tab named "Output" tab by formula but as i add more data, i can see that the file will become larger and slower. therefore, wondering if you folks could help me to automate by writing macros to pull data into "OUTPUT" tab from "Data" tab (you can see how i pulling data with Sumproduct formula) . My request is below:

    I need a Macro that will do exactly what I am doing with the "SUMPRODUCT" formula from cell F5 to AU9 in the "OUTPUT" tab.
    I need to have the flexibility of adding/ deleting more rows or columns as I add/ delete more/ some rows for "Cost_Centre", "Accounts", "Business_Line".
    Is it possible to automate the formula in this Column B?
  • This macro will replace the formulas in columns A and AT of the Data sheet:



    This macro will add all the data to the Output sheet including replacing the formulas:


    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • hi mumps

    many thanks for your kind help. Working like a charm. one small thing.............In my effort to reduce some repeat commands, i realized that the formula/ macro for Column A and AT in "data" tab can just be in Column B and Column AU of "output" tab. is there any way to incorporate this change in the 2nd Macros that you sent? (this way i will not need the 1st macros and just one macro will deliver everything neatly).

  • Place this macro in a regular module not the ThisWorkbook code module.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!