Cell Referencing

[Guest]

It's Tuesday.. which I deem the 2nd Monday and I've only have 1 cup of coffee.

I have a macro that generates a worksheet based on user input. Once the X
number of worksheets are generated a SUMMARY worksheet is generated. This
worksheet will be populated with information from the other worksheets.

Here's the code for the SUMMARY part...

With ActiveWorkbook.Worksheets
.Add before:=.Item(1)
End With
ActiveSheet.Select
ActiveSheet.name = "SUMMARY"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Project #"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Project"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Weekly Hours"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Forecasted"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "% to Forecast"
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]"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MZ6756!R[68]C[1]"
ActiveCell.Offset(1, 0).Range("A1").Select


I want the last 4 referenced cells to see the worksheet but not by name,
also the last two references need to be more like this...

Selection.End(xlToDown).Select

Like I said... it's the 2nd Monday and the caffeine has not kicked in....

THANK YOU!!

 

[Tom Ogilvy]

I assume you mean xldown on the worksheet with the data, not the summary
sheet. Since your formulas are relative to the the location of the formula
on the summary sheet, and your macro is based on whatever cell is the active
sheet, it is unclear where you want to do an end(xldown).

Sub BBBB()
Dim sName As String
ActiveSheet.Name = "SUMMARY"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Resize(1, 5) = Array("Project #", _
"Project", "Weekly Hours", "Forecasted", _
"% to Forecast")
sName = Worksheets(1).Name
ActiveCell.Offset(1, 0).FormulaR1C1 = _
"=" & sName & "!RC[1]"
ActiveCell.Offset(1, 1).FormulaR1C1 = _
"=" & sName & "!RC[1]"
ActiveCell.Offset(1, 2).FormulaR1C1 = _
"=" & sName & "!R[66]C[3]"
ActiveCell.Offset(1, 3).FormulaR1C1 = _
"=" & sName & "!R[68]C[1]"
ActiveCell.Offset(2, 0).Range("A1").Select
End Sub