?Worksheet references without using the sheet "NAME".

turtsmurt

New Member
Joined
Mar 4, 2010
Messages
45
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
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

Well-known Member
Joined
Mar 10, 2004
Messages
784
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

New Member
Joined
Mar 4, 2010
Messages
45
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 :)
 

Forum statistics

Threads
1,136,328
Messages
5,675,137
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top