I have a spreadsheet that I am trying to automatically populate sub total
formulas using a macro. The macro copies a row of formulas goes to a range
and pastes the formulas in that range.

The range is a number of non adjacent cells and changes depending on a
previous selection. I have created a formula in cell B3 that identifies the
new range and creates a text string representing the range:

e.g. 'Class Plans'!$A$6,'Class Plans'!$A$8,'Class Plans'!$A$10

I have then defined a range called "Test" and used the INDIRECT formula to
turn the previous text string into a range e.g. =INDIRECT($B$3)

I have used this approach many times with great success however the range
has always been in adjacent cells.

When I create the non adjacent range normally it works fine; when I try to
create it dynamically the range is invalid.

Has anyone else experienced the same or does anyone know how I can resolve
the problem?