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

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

You can also download the example workbook to test

--

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




"Vsn" <vsn_hotmail_kom wrote in message ...
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,

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" schreef in bericht
...
See this page
http://www.rondebruin.nl/copy2.htm

You can also download the example workbook to test

--

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




"Vsn" <vsn_hotmail_kom wrote in message
...
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

Hi Vsn

Maybe you must change this

Alt F11
Toolsoptions
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" <vsn_hotmail_kom wrote in message ...
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" schreef in bericht
...
See this page
http://www.rondebruin.nl/copy2.htm

You can also download the example workbook to test

--

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




"Vsn" <vsn_hotmail_kom wrote in message
...
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,

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

"Ron de Bruin" schreef in bericht
...
Hi Vsn

Maybe you must change this

Alt F11
Toolsoptions
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" <vsn_hotmail_kom wrote in message
...
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" schreef in bericht
...
See this page
http://www.rondebruin.nl/copy2.htm

You can also download the example workbook to test

--

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




"Vsn" <vsn_hotmail_kom wrote in message
...
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