Error moving sheets between workbooks

Hi

I've written a macro which generates a set of reports based on a report
template and a database imported from Access; this populates the template
with each row and then exports it to a destination file according to the name
of the city and the date (through variables).

I am doing this with a loop function, however this works for a certain
number of iterations but then breaks ("Move method of Worksheets object
failed"), however the number of times this works correctly before breaking is
not always the same and the process is identical for each iteration. I'm not
sure I understand the reason why as the first iterations work with no
problems and at the point where the code breaks down all of the variables are
set correctly - perhaps it is a memory problem?

Also I notice that if I try to move the sheet manually it does not allow me
to move it to the file that the macro should move it to though it will let me
move it to a different workbook.

Any ideas on what might be happening much appreciated.

I believe it is a memory stack problem with moving (copy/paste) sheets
repeatably. You need to save the workbook periodically during a long string
of iterations. Use a counter for your loops and when the counter = 30 (or
whatever works), do a Save.

Mike F
"Keith" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I've written a macro which generates a set of reports based on a report
> template and a database imported from Access; this populates the template
> with each row and then exports it to a destination file according to the
> name
> of the city and the date (through variables).
>
> I am doing this with a loop function, however this works for a certain
> number of iterations but then breaks ("Move method of Worksheets object
> failed"), however the number of times this works correctly before breaking
> is
> not always the same and the process is identical for each iteration. I'm
> not
> sure I understand the reason why as the first iterations work with no
> problems and at the point where the code breaks down all of the variables
> are
> set correctly - perhaps it is a memory problem?
>
> Also I notice that if I try to move the sheet manually it does not allow
> me
> to move it to the file that the macro should move it to though it will let
> me
> move it to a different workbook.
>
> Any ideas on what might be happening much appreciated.

Thanks for this - unfortunately even when I write a counter into the code the
same problem arises (I've tried everything down to saving every single
iteration); I've also tried splitting the code into different subs and then
calling them but this doesn't appear to solve the problem. Is there anything
else I can do to free up memory after moving the sheets?


"Mike Fogleman" wrote:

> I believe it is a memory stack problem with moving (copy/paste) sheets
> repeatably. You need to save the workbook periodically during a long string
> of iterations. Use a counter for your loops and when the counter = 30 (or
> whatever works), do a Save.
>
> Mike F
> "Keith" <[email protected]> wrote in message
> news:[email protected]...
> > Hi
> >
> > I've written a macro which generates a set of reports based on a report
> > template and a database imported from Access; this populates the template
> > with each row and then exports it to a destination file according to the
> > name
> > of the city and the date (through variables).
> >
> > I am doing this with a loop function, however this works for a certain
> > number of iterations but then breaks ("Move method of Worksheets object
> > failed"), however the number of times this works correctly before breaking
> > is
> > not always the same and the process is identical for each iteration. I'm
> > not
> > sure I understand the reason why as the first iterations work with no
> > problems and at the point where the code breaks down all of the variables
> > are
> > set correctly - perhaps it is a memory problem?
> >
> > Also I notice that if I try to move the sheet manually it does not allow
> > me
> > to move it to the file that the macro should move it to though it will let
> > me
> > move it to a different workbook.
> >
> > Any ideas on what might be happening much appreciated.
>
>
>