What I have:

A sheet that is named all containing 40 columns of information. Every day
or two new information is added to the next row. Column 40 contains the
names Bill, Joe, Ted, Frank, Steve.

I have a macro that will take each name and make a separate sheet for each.
The macro will delete the old name. The names don't change, nor do their
spelling. The only things that will change are new rows are added and a new
name may be added.

In a cell, I use sum to add up a specific column from each sheet.
=SUM(Ted!Q2:Q161)

What happens is that when I run the macro to replace the sheets, I get
=SUM(#REF!Q2:Q161)

I assume that because the spelling of the sheet name doesn't change the
formula should work. I assume wrong. Any suggestions?