Condense code

I am currently learning to condense code by turning several macros that are similar into one that I can use for all.

The below is the last bit that I need to change to have a single macro instead of 7.

At this point, I am copying the ActiveSheet and a sheet called "Invoice" to a new workbook, then adding the formula to the sheet called "Invoice":

Please Login or Register  to view this content.

How do I get the sheet name in each formula to change each time so that it refers to the sheet in the workbook that isn't "Invoice"?

Thanks in advance.

there's no need to select a sheet:

Please Login or Register  to view this content.

But this overwrites the same cell 7 times (?)

Even better snb, thanks for your suggestion.

I'm using the following now (ADV sheets are a special case that need a bit extra info, and slightly amended formula to SUM):

Please Login or Register  to view this content.

-edit: The workbook will consist of 1 of the following 7 combinations of sheets:

"Invoice" and "Completes"
"Invoice" and "Aborts"
"Invoice" and ...
"Invoice" and ...
"Invoice" and ...
"Invoice" and ...
"Invoice" and ...

So the formula is only entered once on "Invoice" to sum the backing data.

Again, many thanks both for your help.

Please Login or Register  to view this content.