This is a long sought after and requested feature, and finally in 2008 R2 it’s here.
The property that you are looking to set is:
Set this and then on export to excel the tab will use the page name.
This can be set on certain objects in the report and can use Expressions to set the name.
Objects that contain this property include:
As an example of how it works, I have a report that runs as 2 sub reports. Each subreport has a tablix that has the page name property set.
When the report exports to excel I get 2 tabs each named as defined in the page name property.
Another more complex example is using a tablix with grouping. Set the tablix to page break on the element that you are grouping by and then set that element as the value for your page name. Each time the grouping changes it will render in a new page and if exported to excel each tab will appear with the name of the group.
This post goes into it in more detail