something like

sheets(range("A1").value).range....

would be what you want I think

Francois via OfficeKB.com wrote:
Is it possible to have the sheets reference read the contents of a cell eg
Sheets(A1) where A1 contains JUN06 and would change if I altered A1 to
JUL06 ?

Sheets("JUN06").Range("E5:AO10000").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("E1:F2"), CopyToRange:=Range("C6:AJ6"), Unique:
=False
rows("6:6").Copy

rest of macro etc

--
Message posted via http://www.officekb.com