I don't believe there is a solution to this problem but here it comes!
I have a report with multiple tables and tablixes with different number of columns as well as column widths. In the process of making the export to excel as friendly as possible, I had to break it into multiple excel sheets, in order to avoid double columns and/or added blank columns.
Is there a way to keep everything together in one page on the report preview and the splitting to occur only when exporting to excel?
Not sure what to try, assigning different page names to tablixes but without the page breaks keeps everything in one page both on preview and on export.
You can do this by adding rectangles at the points where you want the page breaks. These can be as small as you like as they only serve to give a page break.
Set the page break properties on the rectangles to 'after'. Set the page name on the rectangles as you need.
Finally, set the Hidden property of the rectangles to =Globals!Renderformat.Isinteractive. This means that when the report is being viewed as default Isinteracive will be true and the rectangles will be hidden and the page breaks ignored.
When rendered in any other format (e.g. Excel) the opposite will be true and the rectangles will be rendered along with their associated page breaks.