I don't think I'd use activecell and relative addressing. Since those formulas
depend on the cell you're in, it can get really confusing.

But you could plop formulas into a summary worksheet if you want:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim sumWks As Worksheet
Dim oRow As Long
Dim LastRow As Long

Set sumWks = Worksheets("summary")
oRow = 1 'some starting row

For Each wks In ActiveWorkbook.Worksheets
With wks
If .Name = sumWks.Name Then
'do nothing
Else
oRow = oRow + 1
sumWks.Cells(oRow, "A").Formula _
= "=" & .Range("a1").Address(external:=True)
sumWks.Cells(oRow, "B").Formula _
= "=" & .Range("b1").Address(external:=True)
'find lastrow of each worksheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
sumWks.Cells(oRow, "C").Formula _
= "=" & .Cells(LastRow, "A").Address(external:=True)
End If
End With
Next wks
End Sub

This creates a formula for A1, B1 and based on the last used cell in column A,
C###.




Alex wrote:

I found this on here a couple of days ago...

With ActiveCell
Set SumRange = Range(.Offset(-1), .Offset(-1).End(xlUp))
.Formula = "=sum( " & SumRange.Address(RowAbsolute:=False,
ColumnAbsolute:=False) & ")"
.Font.name = "Arial"
End With

This worked perfectly after I changed the offset ranges.

Now I have a question regarding referencing cells for data, as follows.

I have a maco that will generate reports as needed. Now I'm trying to wrap
it up to where it generates a summary worksheet. The thing is trying to get
all the data from the various worksheets that were generated on to the
summary worksheet. The generated worksheets are all unique in the number of
rows of data but have the same number of columns. I originally had a custom
macro just for myself and it took care of it all and worked great but now I'm
trying to port it out to other people where I work.

ActiveCell.Offset(1, -4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MZ6756!RC[1]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MZ6756!RC[1]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MZ6756!R[66]C[3]"

As you can see it gives the absolute value even though when I was recording
it as being relative. The question I have is, if I used the code up top,
modified to include the needed ranges, would it work? Would I need to add an
array or just reset the variable for each cell request? Other suggestions?

THANKS!!

--
Jack of all trades... master of none..

--

Dave Peterson