Overflow Error - Faster way of copying through macro

**c.vaibhav** · 11-19-2009, 12:32 AM

Hi,
I am transferring 10000 rows from 1 workbook to another..

It takes place smoothly for the first 30000 rows, but after that on the 'select' line it gives me an overflow error.

The code that I am using is as following:

Please Login or Register  to view this content.

Here xlverfile is the other workbook which changes with every for loop..

Hope I can get some solution.. I get the error on the .select line.

Regards,
Vaibhav

**c.vaibhav** · 11-19-2009, 12:46 AM

oops.. I am so sorry..

I Had declared i as integer.. it should have been long..

regards,
vaibhav

**broro183** · 11-19-2009, 01:34 AM

hi vaibhav,

Your code can be shortened to the below (change the "Range("a123")" as needed.
".Select" & ".activate" are rarely needed in macros & modifying your code to work without them will help your code run faster.

Please Login or Register  to view this content.

hth
Rob

**c.vaibhav** · 11-19-2009, 04:29 AM

Hi Rob,

Thanks for the help..
but the code doesnt seem to work on my PC..

that screenupdating part is fine.. however the one line code for copying and pasting is not working..

i get the following error:

Copy Method of range class failed.

Error - 1004

Probably because I am copying across workbook.. Also these two workbooks are not in the same instance of excel.. I have opened a new instance of excel..

Is there any faster way to deal with my problem.

Regards,
Vaibhav

**shg** · 11-19-2009, 11:03 AM

Also these two workbooks are not in the same instance of excel..

That's not going to work.

**broro183** · 11-19-2009, 06:22 PM

I'm having trouble understanding how the original version worked, if the other file (how is xlverfile defined?) is in a separate instance of Excel. Does it need to be?

Rob

**shg** · 11-19-2009, 06:41 PM

Reckon you could paste from the Office clipboard, but wonder why the need to be open in separate instances.

**c.vaibhav** · 11-20-2009, 11:46 AM

I am no more facing the overflow error.

I'll explain you why I am using different instances of excel..

Actually the new files thus created needs to processed. i.e. I have a toolbar which has the macro in it. If I open the workbooks in the same excel then I would not be able to run the toolbar macro on each of these workbooks simultaneously.. rather If I open the new workbooks in a new instance of excel then I can run the toolbar Macro simultaneously and my work gets divided. The toolbar macro takes a long time to process the data..

I was wondering if there could be a faster way of copying 10000 rows across workbooks in different excel instances..

Regards,
Vaibhav

**broro183** · 11-30-2009, 05:37 PM

Hi Vaibhav,

I am no more facing the overflow error.

If the problem of this thread is solved, can you please post your final code (for others who come across the thread while searching), & mark the thread as Solved?

I suggest starting a new thread about your speed issue & posting all your processing code. Someone may be able to suggest ways of optimising/speeding it up so dramatically that your files no longer need to be divided!
Edit: To provide background you could provide a link to this thread in your new thread.

Rob

**c.vaibhav** · 12-02-2009, 01:20 AM

ohhh.. actually the error got resolved just by changing the data type from integer to long..

I really need to reduce the processing speed.. but the logic of macro has become too complex..

I surely will have to start a new thread.. thanks rob for the suggestion..

regards,
vaibhav

**broro183** · 12-02-2009, 06:45 PM

Good as gold, chuck a link to the new thread in to this one when you create it...

Rob

Overflow Error - Faster way of copying through macro

LinkBack

Thread Tools

Rate This Thread

Display

Overflow Error - Faster way of copying through macro

Re: Overflow Error while copying 10000 rows from one workbook to another

Re: Overflow Error while copying 10000 rows from one workbook to another

Re: Overflow Error

Re: Overflow Error - Faster way of copying through macro

Re: Overflow Error - Faster way of copying through macro

Re: Overflow Error - Faster way of copying through macro

Re: Overflow Error - Faster way of copying through macro

Re: Overflow Error - Faster way of copying through macro

Re: Overflow Error - Faster way of copying through macro

Re: Overflow Error - Faster way of copying through macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions