using cells values to determine macro action

[simonsmith]

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

 

[Tom Ogilvy]

Dim sh as Worksheet, sh2 as Worksheet
Dim i as Long, ja s Long
Dim rng as Range, cell as Range
set sh = Worksheets("Sheet1")
set sh1 = Worksheets("Compiler")
j = 1
do while sh.Name <> sh1.Name
set rng = sh.Range(sh.Range(sh.Range("P3")), _
sh.Range(sh.Range("P4")))
i = 0
for each cell in rng
i = i + 1
sh1.Cells(i,j).Value = cell.value
next
j = j + 1
set sh = Worksheets(sh.Range("P5"))
Loop
sh1.Activate

 

[simonsmith]

Hi Tom,
thanks very much for the code, much appreciated. I found the following

When it gets to this point it stops
set sh = Worksheets(sh.Range("P5")) this line is red in the code.

Also on the sheet 1, the range on the first sheet only is copied, and
it pastes it to a single column, column A, in the compiler sheet
instead of pasting it to the same location as it came from on the other
sheet 1 (i.e. the start and stop cells specified). I can send you a file
to look at if you wish.

Any help very much appreciated and if it's too tricky don't stress
about it

Cheers

Simon