Is it possible to assign the name name to ranges on different worksheets?
You bet, as long as the scope of the name is the worksheet of interest. In
Excel 2007, under FORMULAS -> DEFINE NAME, it defaults to WORKBOOK, but you
can change it to the worksheet of interest.
If you want to do it programmatically, do something like this
aWS.Names.Add Name:=myRangeString, RefersTo:=myRefersTo
I have defined myRefersto this way in my code
myRefersTo = "='" & aWS.Name & "'!" &
where myRange defined as a range in my code.