In Reporting Services 2008 R2 Microsoft has added functionality to rename multiple sheets, so if your report is more than one page and you want to rename the worksheets this will work automatically out of the box.
You can either rename the sheets for a group, if each group has a page break between them or you can rename sheets if there is page break between objects.
To rename sheets in a table with groups all you have to do is select the group as shown below and then in the properties window (press F4 if the properties window is not shown) change the setting for pagename under the Group section to the value you want. This could be text or a field from your dataset.
The export to excel will rename the sheets with the group name.