Many to One (Excel sheet)

[Vsn]

Hi all,

I am not so familiar wit VBA for excel, more with the Access version. That
is as well were my question comes from. Any help is appriciated.

I have an excel file which contains 37 (!!) sheets, all in the same data
format full with tons of data. I would like to, automaticaly with some code
if possible, copy all the contents of each sheet to one new (to be created
sheet). Is this do-able?

Afterward I will import the newly created sheet into an Access table and
create a usefull datebase around it.

Thx alot, cheers,
Ludovic

 

[Ron de Bruin]

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

You can also download the example workbook to test

 

[Vsn]

Ron,

Very impresive exact what I was looking for.

However, I downloaded your example file and expirianced two crashes:-

On the first run (any example) when the sheet 'RDBMMergeSheet' does not
excist yet is gives error '9', Subscribt out of range in line
'ActiveWorkbook.Worksheets("RDBMergeSheet").Delete'. After proceeding from
the line under it 'On error GoTo 0' the sheet seems to be created and all is
copied.

On any following time (any example) it find a crash on error '91', Object
variable or With block variable not set' in function LastRow line 'LastRow =
sh.Cells.Find(What:................'

Strange thing that apperantly it seems your the code seems not to adhere to
the line 'On Error Resume Next' either.

All findings without changing any thing in your example sheet. I do use an
Ecel version of Office 2003 (11.8307.8221 SP3). What can I do wrong, do I
need to load a reference (apart from the Microsoft Excel 11.0 Object
Library)?

Can you advice. I was just so delighted with this tool, which would save me
a lot of messing around.

Regards,
Ludovic

 

[Ron de Bruin]

Hi Vsn

Maybe you must change this

Alt F11
Tools>options
General
Maybe break on all errors is checked

Check Break on all unhandled errors

--

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

 

[Vsn]

Ron,

As you adviced I have changed the setting to 'Break on unhandeled errors'
and all works fine.

To be short; a really magnificent tool you created here! Thanks a lot for
your efforts!

Cheers,
Ludovic