Convert all the formulas into values except the rows contain Sum

[kay007]

Dear Experts,

I have a macro that extracts worksheets from a central workbook into a new workbook and with a short macro that converts the formulas contained into values. However, i am looking for a simple code that would convert all the other formulas to values except the rows that contain excel function "Sum() or Subtotal()". Find below the current code used in converting to values.

Please Login or Register  to view this content.

Many thanks in advance for your help.

[FDibbins]

I cant help with your question , but I would ask - if you are already converting formulas to values, and those values go into a SUM or SUBTOTAL, if that is all that makes up those SUM, thejn why not just convert them too? (I understand possibly wanting to keep teh SUBTOTAL

[kay007]

Keeping the sum formulas rather than converting them to values is to help the users that might want to further insert lines or data and wouldnt want to start adding all the sum formulas to get the their most up to date figures.

[Trebor76]

Hi kay007,

Try this (initially on a copy of your data as the results cannot be undone if they're not as expected):

Please Login or Register  to view this content.

Regards,

Robert

[FDibbins]

Keeping the sum formulas rather than converting them to values is to help the users that might want to further insert lines or data and wouldnt want to start adding all the sum formulas to get the their most up to date figures.

OK, thats makes sense

[kay007]

Thanks Robert for that code..However, the macro wiped out all the formulas including the cells containing "SUM" or "SUBTOTAL".

[skywriter]

Try changing the word "OR" to "AND" in this line of code.

Please Login or Register  to view this content.

[kay007]

Thanks Skywriter. I tried your suggestion and i noticed that the "SUMIF" were not converted to values like the others. I only want the Sum and subtotal functions to be the available ones in the report. All others should be converted to values..

Many thanks once again for your time.

[JOHN H. DAVIS]

Can you provide a sample sheet with some dummy data?

[kay007]

Thanks all for the time taken so far in helping out. Much appreciated

Please find attached a sample report.

Book4.xlsx

All the other formulas should be values except the row containing Total (Sum or Subtotal).

Many thanks once again.

[Catman50]

Try This Slight amendment

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[kay007]

That works fine Catman50. Thanks a lot Catman50, Trebor76,FDibbins, and skywriter for taking out time to work on this issue.

Very much appreciated.

Regards,
Kay007

[kay007]

Thanks JOHN H. DAVIS. it worked on my sample data as well. Many thanks