1004 - Move Method of Worksheet Class Failed

[Buddhaful]

I have a pretty lengthy single module in an excel file which runs fine
when the input file consists of approximately 8,000 or less records.
However, if the input file has 9,000 records or so then the macro
fails at a point when it is trying to move the worksheet from the
input file (a .txt file which is read in as pipe-delimitted and then
parsed) to another excel file which takes the input file data and
performs additional parsing....etc. etc.

I have seen lots of discussion regarding 1004 error messages, but not
anything related to erros associated with what I am experiencing which
is definitely tied to the number of rows from the original text file.

Currently, I am just breaking the input file into 2 and running the
process twice. I'm hoping there is a better solution?

Any ideas?

Thanks in advance for your help.

 

[Joel]

I'm just guessing, put I suspect is may be the way you are copying an d
pasting that is causing the problem. when you do a copy and paste it is
using the clip board to store the data. You may be running out of space. I
would recommend doing the copying witthout using the clip board.

rather than
MyRange.copy
Newrange.paste


try
MyRange.copy destination:=NewRange - doesn't use clipboard.

 

[Buddhaful]

I'm just guessing, put I suspect is may be the way you are copying an d
pasting that is causing the problem. when you do a copy and paste it is
using the clip board to store the data. You may be running out of space. I
would recommend doing the copying witthout using the clip board.

rather than
MyRange.copy
Newrange.paste

try
MyRange.copy destination:=NewRange - doesn't use clipboard.









- Show quoted text -

I'm doing a move worksheet (fyi - copy/paste will not work as some
columns have a lot of info & the copy results in an error):

Windows("Input.txt").Activate
Sheets("origdata").Select
Sheets("origdata").Move Before:=Workbooks("FinalData.xls"). _
Sheets("Output")

Again, this has to be a memory issue or something else of a similar
nature. This program runs fine when the input data set has less than
8,000 rows. It is only when the input dataset exceeds 8,000 that I
get the error message.

 

[Joel]

Three things I would do

1) Remove Sheets("origdata").Select. This statement is not needed. My be
the cause of the error. I also think Windows("Input.txt").Activate is not
needed but not sure. this statement I don't thinnk would cause you error,
but you can try to remove it and see what happens.

2) On the excel worksheet - View Menu - Task Pane. when the window open
selct under the down triangle (next to the X) clipboard. When the program
fails see if there are lot of entries in the clipboard. I suspect that
moving between sheets is using the clipboard.

3) If the clipboard is really being used, then generate a new posting with
the code you sent me and see if anybody has a solution. The question is how
not to generate code that will not use the clipboard or a way of clearing the
clipboard.