Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
simonsmith
 
Posts: n/a
Default Asked previously...can this not be done in excel


Hi there,
Does anyone know how to do this with a macro? (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=542712

  #2   Report Post  
Posted to microsoft.public.excel.misc
davesexcel
 
Posts: n/a
Default Asked previously...can this not be done in excel


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 to
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 rewarding


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=542712

  #3   Report Post  
Posted to microsoft.public.excel.misc
beatrice25
 
Posts: n/a
Default Asked previously...can this not be done in excel


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


--
beatrice25
------------------------------------------------------------------------
beatrice25's Profile: http://www.excelforum.com/member.php...o&userid=34505
View this thread: http://www.excelforum.com/showthread...hreadid=542712

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
unhide menu bar in excel - just disappeared Sean Setting up and Configuration of Excel 12 April 4th 23 10:19 AM
Excel 2003, Convert EXISTING Worksheet Data to XML? [email protected] Excel Discussion (Misc queries) 4 November 16th 05 05:45 AM
Opening two separate instances of Excel Ron Bishop Excel Discussion (Misc queries) 2 August 4th 05 05:30 PM
Hints And Tips For New Posters In The Excel Newsgroups Gary Brown Excel Worksheet Functions 0 April 15th 05 05:47 PM
Difference in number of Excel NewsGroups Hari Prasadh Excel Discussion (Misc queries) 1 January 25th 05 12:32 PM


All times are GMT +1. The time now is 03:13 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"