The solution to this may or may not be VBA but I'll post it anyway in the hope that I can avert the mind-deadening tedium:
  1. I have multiple rows with 27 columns of Sales by product, running horizontally across the page.
  2. For a Pivot table I need to link to these entire ranges, however the data for the Pivot table has to run vertically, in a single column, i.e. it needs to be transposed.
  3. I tried to do this by naming the ranges but I may be doing it incorrectly because I get a #VALUE! error
It would have been easy enough if I simply had to transpose and copy the data however in this case I need to have live links. Is there a way to do this in an "automated" fashion without having to link to each cell individually by pointing and clicking?

Many thanks!