Asked previously...can this not be done in excel

[simonsmith]

Hi there,
Does anyone know how to do this with a macro? (I could do it wit
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 wit
its own data. I want to create a new grid on another sheet comprised o
segments of these other grids on these sheets. Some type of loopin
macro is needed to do the following.

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

Here is an example
(Note each sheet except the compiler sheet will have P3 as its star
copying cell reference, P4 as its stop copying cell reference and P5 a
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 I
onto the “compiler” sheet, it will then go to the sheet named in P5 (i
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 wil
be copying B8, D8, E8 onto the compiler sheet. The macro will then rea
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 an
including B9 and I39 then go to the Compiler sheet where the macro wil
end.

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


Cheers

Simo

 

[davesexcel]

have you tried the macro recorder?

You may have to do it one piece at a time and then put it together
somethings can't be done on the macro recorder and when you come t
that spot,
ask if anyone has a vba code to do such and such, anyway
try the macro recorder, your own success will be very rewardin

 

[beatrice25]

Thanks Dave, the macro recorder won't allow for my entries in the start
and stop copy points and go to next sheet, what I need is some clever
and nice guy to show me what code is needed.

Thanks anyway

Simon