Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
simonsmith
 
Posts: n/a
Default 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


--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
View this thread: http://www.excelforum.com/showthread...hreadid=542365

  #2   Report Post  
Posted to microsoft.public.excel.misc
mrice
 
Posts: n/a
Default Compiling macro based on cell values


If you use formulae like...

X = Range(Cells(4, 2))

In this case Cell B4 has the value A1


This will allow you to indirectly address cells


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=542365

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Format cell color based on multiple cell values Zenaida Excel Discussion (Misc queries) 3 May 9th 06 11:56 PM
Conditional coloring of Excel cells, based on adjacent cell values? Greg Stuart Excel Worksheet Functions 0 March 10th 06 11:14 PM
Row Expansion Susan Excel Worksheet Functions 11 February 28th 06 08:15 PM
Clear Cell based on event w/o macro? Paul987 Excel Worksheet Functions 2 October 27th 05 08:44 PM
Cell values based upon multiple conditions Ryan M via OfficeKB.com New Users to Excel 2 July 19th 05 02:10 AM


All times are GMT +1. The time now is 09:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"