Hi Everyone,

I need to consolidate 31 sheets on to one sheet in the same workbook.
Each sheet has 7 columns (they are all the same). The range of data
that I need to consolidate from each sheet is always b5:h125. The
issue that I'm having is that 3 of the columns are validated lists
(with help from Debra Dalgleish) and 2 are equal to an initial entry
with a formula equalling the cell above for the rest of the column.

I tried, with limited success, to use DataConsolidate option but that
would only consolidate the data that was actually entered manually
into the sheets rather than the values that were chosen from the drop
down lists.

Is there any way I can consolidate these values in one sheet?

Thanks,

Dani

if you select B5:H125, do edit=copy

then go to the other sheet and select the top left corner where you want the
data and do
Edit=Paste Special and select values

does that give you the information you want?

--
Regards,
Tom Ogilvy


"CloudDoctor" wrote:

Hi Everyone,

I need to consolidate 31 sheets on to one sheet in the same workbook.
Each sheet has 7 columns (they are all the same). The range of data
that I need to consolidate from each sheet is always b5:h125. The
issue that I'm having is that 3 of the columns are validated lists
(with help from Debra Dalgleish) and 2 are equal to an initial entry
with a formula equalling the cell above for the rest of the column.

I tried, with limited success, to use DataConsolidate option but that
would only consolidate the data that was actually entered manually
into the sheets rather than the values that were chosen from the drop
down lists.

Is there any way I can consolidate these values in one sheet?

Thanks,

Dani

On Apr 3, 12:25 pm, "CloudDoctor" wrote:
Hi Everyone,

I need to consolidate 31 sheets on to one sheet in the same workbook.
Each sheet has 7 columns (they are all the same). The range of data
that I need to consolidate from each sheet is always b5:h125. The
issue that I'm having is that 3 of the columns are validated lists
(with help from Debra Dalgleish) and 2 are equal to an initial entry
with a formula equalling the cell above for the rest of the column.

I tried, with limited success, to use DataConsolidate option but that
would only consolidate the data that was actually entered manually
into the sheets rather than the values that were chosen from the drop
down lists.

Is there any way I can consolidate these values in one sheet?

Thanks,

Dani

You have probably already thought of this but you can use the Paste
Special Paste Values and the "validated" information will appear.
This would be a manual process that none enjoys but I have no code
right now that would speed it up.

If this is a once in a life time event, after you have selected the
sheet where you are going to copy from, record a macro of you copying
the information and assign it a "shortcut key" (example: ctrl+L).

Next, select the cell where you want to paste and record another macro
of you pasting the information. Again, assign it a "shortcut
key" (example: ctrl+N).

This way, while you gather your info, all you have to do is select
your sheet, you want to copy from, press ctrl+L select an empty cell
on "Sheet1" and press ctrl+N

I do this when I am doing an adhoc task that requires speed over
finesse because how long it takes to perfect some VBA code.

Hi Whicks,

Thanks for the tip on using Macros... unfortunately this will be a
monthly task :( so will plough on and see if I can find some
code....

Dani

See this page CloudDoctor
http://www.rondebruin.nl/copy2.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CloudDoctor" wrote in message oups.com...
Hi Whicks,

Thanks for the tip on using Macros... unfortunately this will be a
monthly task :( so will plough on and see if I can find some
code....

Dani

Thanks Ron,

This is really helpful - very neat code!

CloudDoctor