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