?Worksheet references without using the sheet "NAME".

[turtsmurt]

Can anyone tell me how to use the below code for different workbooks where the Sheet Names are going to change in each workbook? I am a novice at writing code (as I am sure you can tell). We have a program set up that provides us with a daily workbook. I have a macro that creates a STATS worksheet in each new workbook, but I am entering the formulas manually daily because I don't now how to have the macro refer to the proper sheets. IE: Everyworkbook's worksheets are set up in exactly the same order, so in the code below, I want to refer to the 2nd sheet AFTER this sheet or the 4th sheet AFTER this sheet...etc

Any help would be greatly appreciated.

Sub StatsFormulas()
'
' StatsFormulas Macro
'
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "='LateScanOuts 3-18-10'!RC[14]"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM('LateScanOuts 3-18-10'!C[11])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNT('LateScanOuts 3-18-10 OVER 30'!C[-3])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM('LateScanOuts 3-18-10 OVER 30'!C[9])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNT('ROC North'!C[-5])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNT('ROC South'!C[-6])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNT('Major Invest'!C[-7])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNT('Home Secure'!C[-8])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNT('LateScanOuts 3-18-10 OVER 30'!C[9])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM('LateScanOuts 3-18-10 OVER 30'!C[8])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNT('8X4'!C[-11])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM('8X4'!C[1])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('LateScanOuts 3-18-10 OVER 30'!C[2],""10AX6P"")"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "='3-19-09'!R[-1]C"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNT('3-19-09'!C[-15])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM('3-19-09'!C[-3])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNT('3-19-09 OVER 30'!C[-17])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM('3-19-09 OVER 30'!C[-5])"
Range("A1").Select
End Sub

Christine

 

[AlphaFrog]

If sheet "LateScanOuts 3-18-10" is the 1st sheet, then replace this...
ActiveCell.FormulaR1C1 = "='LateScanOuts 3-18-10'!RC[14]"

With this...
ActiveCell.FormulaR1C1 = "='" & Sheets(1).Name & "'!RC[14]"

Sheets(1).Name will get the name of the 1st sheet

 

[SamTYler]

do the worksheet names change according to a fixed pattern?

If so, what is the pattern?

Or you could use the InputBox Function to enter the name of the sheet you want. Put it in the top of your macro

 

[turtsmurt]

If sheet "LateScanOuts 3-18-10" is the 1st sheet, then replace this...
ActiveCell.FormulaR1C1 = "='LateScanOuts 3-18-10'!RC[14]"

With this...
ActiveCell.FormulaR1C1 = "='" & Sheets(1).Name & "'!RC[14]"

Sheets(1).Name will get the name of the 1st sheet

Thanks, I'll give this a try and let you know. I appreciate the help.
Christine