Sum Column from one sheet to specific cell on another (sheets get deleted every month)

[brgr4u]

Hello,

I am trying to sum up a column with a heading "Total Charges" on one sheet (always the 9th sheet, but deleted every month) and input the value into cell B8 on another sheet. The sum will be displayed will always be called "Summary."

I am at a loss on how to write the sum formula that will display the value on "Summary" without explicitly referencing the sheet name with the source data.

Thank you for any help you can provide!

[majosum]

If you name the range of cells that you want to add (for example: "CellsToAdd"), then you could just write a formula that says =SUM(CellsToAdd)

[IgorSharov]

Here is my solution. Try it. Book1.xlsm

[brgr4u]

Thank you IgorSharov, but it doesn't seem to be taking the sum of the numbers. The data set I have now currently totals ~$975,000 and your macro is returning a value in excess of ~$20 billion.

Sorry for the delayed response, traveling for work.