Compiling macro based on cell values

Hi there,
Does anyone know how to do this? (I could do it with multiple formulas but would end up with a spreadsheet from hell)

I have several sheets, each with a grid of cells B6 to I45 filled with its own data. I want to create a new grid on another sheet comprised of segments of these other grids on these sheets. Some type of looping macro is needed to do the following.

The macro will start on a “base” sheet and start copying cells from the B6 to I45 grid onto a “compiler” sheet. I need to be able to specify where to start and stop copying on the base sheet. (via some input data cells eg Cell P3 contains start cell reference, Cell P4 contains stop cell reference ) Once the macro has reached the specified stop cell it must then go to another “input” cell (e.g. P5) which will contain the name of the sheet it must then go to. The macro will then go to that specified sheet and continue compiling values from that sheet onto the compiler sheet. Each sheet will contain P3,P4,P5 to tell the macro where to start and stop copying cells from and then which sheet to go to next. This start copying from this cell and stop copying at this cell routine will continue until a sheet has cell P5 = “Compiler”. On this sheet the macro will copy the last set of specified cells to the compiler sheet (as specified on P3, P4), then read P5, go to the compiler sheet and stop.

Here is an example
(Note each sheet except the compiler sheet will have P3 as its start copying cell reference, P4 as its stop copying cell reference and P5 as the name of which sheet to go to next.


Sheet 1 inputs
(start cell) P3= B6
(stop cell) P4=I7
(go to)P5= SHEET 2

On “Sheet 1”, the Macro will copy all cells B6 to I6 and then B7 to I7 onto the “compiler” sheet, it will then go to the sheet named in P5 (in this case it is sheet 2)

Sheet 2 inputs
(start cell) P3 = B8
(stop cell) P4 = H8
(go to) P5 = Sheet 3

On “Sheet 2” the macro will again copy cells as but this time it will be copying B8, D8, E8 onto the compiler sheet. The macro will then read the value of P5 on sheet 2 which tells it to go to “Sheet 3”

Sheet 3 inputs
(start cell) P3 = B9
(stop cell) P4 = I39
(jump to) P5 = Compiler

On “Sheet 3” the macro will copy all cells as specified between and including B9 and I39 then go to the Compiler sheet where the macro will end.

Can this be done or not? Any help very much appreciated


Cheers

Simon