This problem has a bit of history, but I will cut it down to the bare
bones. I have a workbook that starts with three sheets called Semester
1, Semester Template, and Totals. I have a macro on Totals that creates
a sheet for the next semester before Semester Template, in this case it
would be Semester 2. Then, the macro inserts a number of columns equal
to the number of Mondays in the semester. For instance for Fall 2005,
there are 27 weeks from August 1-January 31 (I need our semesters to
overlap for the project, so Fall ends in January.) so there are 27
columns between A and the last column headed as Total. This Total
column for the semester sheet has an INDIRECT formula for summing that
particular row from B:[Total column - 1]. These totals work great. The
last the thing macro does is create a column on Totals to reference the
semester Total column from the new semester sheet that was created. In
the appropriate cell, I need to reference the corresponding category's
Total on the semester sheet. I have attempted to do this a number of
ways, but all to no avail. The code I have should work in my limited
understanding but it doesn't. Any help would be immensely appreciated.

ActiveCell.Value = "=INDIRECT(ADDRESS(4," & NewSemLastCol & ",,," &
NewSem &")"

The address I am referencing with the above code contains this formula:

=SUM(INDIRECT("B4:"&ADDRESS(ROW(),COLUMN()-1)))

NewSemLastCol is an integer variable that is equal to the number of
Mondays in the semester plus two, which yields that last column number
on the semester sheet. NewSem is a variable containing the name of the
sheet for the newly added semester. NewSem works for sure because I
have used it in another reference on the Totals sheet.

Sheets("Totals").Activate
Range("A1").Select
ActiveCell.Offset(0, LastSheet - 1).EntireColumn.Insert
ActiveCell.Offset(0, LastSheet - 1).Value = "='" & NewSem & "'!A2"
ActiveCell.Offset(0, LastSheet - 1).Columns.AutoFit

NewSem!A2 is the term and year, i.e. Fall 2005, Spring 2006, etc. on
the semester sheet. The code above puts the term and year as the column
heading in the Totals sheet's column for that semester's totals. The
only difference between NewSem!A2 and the cell I am trying to reference
is that A2 has a string while the cell I want to reference has the
SUM(INDIRECT()) formula from above. When I enter the cell reference in
Excel as

='Semester 2'!AC4 (which is what the reference works out to with the
NewSemLastCol and NewSem variables)

the total value from that cell shows up nicely in my Totals sheet. I
just cannot for the life of me get it to work in VBA. Thanks in advance
for your help. If I need to post more info/data, I will be happy to do
so.

Anyone?

Well, I think I have figured out part of the problem. My INDIRECT
formulas that are at the end of every row of data appear to be creating
circular references. But I'm not sure how to go about correcting this.
I tried to figure the address of the last cell to total and then add it
to my formula like this

Dim r as Integer, i as Integer, MyNumbers as Variant
r = 4
MyNumbers = Array(1,1,1,3,1,1,3,1,1,2,1)
LastCellToTotal = Cells(r,NewSemLastCol - 1).Address
Range("A1").Activate
ActiveCell.Offset(3,NewSemLastCol).Select
ActiveCell.Formula = "=SUM(B" & r & ":" & LastCellToTotal & ")"
For i = 0 to 10
ActiveCell.Offset(MyNumbers(i),0).Formula = "=SUM(B" & r & ":" &
LastCellToTotal & ")"

I thought that by figuring out the actual address of the cell before
the total cell where the formula goes, I could avoid the circular
reference problems. So far, I haven't made much progress with this.

Wow, I didn't put my latest version of that code in. It should be like
this.

Dim r as Integer, i as Integer, MyNumbers as Variant
r = 4
MyNumbers = Array(1,1,1,3,1,1,3,1,1,2,1)
LastCellToTotal = Cells(r,NewSemLastCol - 1).Address
Range("A1").Activate
ActiveCell.Offset(3,NewSemLastCol).Select
ActiveCell.Formula = "=SUM(B" & r & ":" & LastCellToTotal & ")"
For i = 0 to 10
r = r + MyNumbers(i)
ActiveCell.Offset(MyNumbers(i),0).Formula = "=SUM(B" & r & ":" &
LastCellToTotal & ")"
Next i

Also, the use of the Array might need explaining. I have some rows in
the column that are not going to have a formula and some that have a
formula hard coded in a template. The layout of the sheet will not
change, so I can hard code the series of offsets into an array like
this, no?

Wow, I hate/love when the solution is so very simple!! Rather than
referencing the cells after adding in the columns for the semester's
weeks, I used relative references and input them before adding the
columns, thereby adjusting the cell reference automagically. I hope
this might help someone else in a similar situation!