Excel Noob with question on referencing worksheet names

[sloojer]

Hello

I am trying to dynamically size a table in my "Summary" sheet with the number of "WPO" sheets in my workbook.

The WPO sheets have names of the form "WPO 1".... "WPO 100"..etc. For each WPO sheet I would like to insert a row in my summary sheet containing formulae that reference the different WPO sheets.

Here's a snippet of what I have at the moment

ActiveCell.FormulaR1C1 = = "='WPO 2'!R64C3" 'Column E

What I would like to have is a variable in the place of 'WPO 2' that I could set to any worksheet.

 

[sloojer]

Sorry colour flags messed that line up..

ActiveCell.FormulaR1C1 = "='WPO 2'!R64C3"

 

[cherria]

you would wrap your code in a FOR loop

if you know th enumber then

FOR a=1 to 100
ActiveCell.FormulaR1C1 = = "='WPO " & a & "' !R64C3" 'Column E
NEXT

If you do not know th enumber of sheets then

FOR EACH a in activeworkbook.worksheets
ActiveCell.FormulaR1C1 = = "='" & a.Name & "' !R64C3" 'Column E
NEXT

 

[sloojer]

Thats great! Thanks!