I have a macro that takes data from a list and uses this to create a model in
a separate sheet (called 'model'). It then takes a copy of that sheet
(creating 'model (2)') and then renames the model with the person's name to
whom it relates (e.g. model_john').

It then repeats the process using the next column of data.

All works well for about 20 columns/models (it is always the same number)
and then I get an error 1004 at the command:

Sheets("model").Copy Befo=Sheets("INDEX")

Furthermore, if I then try to copy a sheet manually, nothing happens.
However, if I open a new book, I can copy a sheet in that and, when I return
to the problem book, I can copy sheets again.

It is Excel XP running on Windows XP. The workbook is read only. There is
no protection applied.

Ian

Try saving the workbook every 10 sheets/Columns.

--
Regards,
Tom Ogilvy


"IanC" wrote:

I have a macro that takes data from a list and uses this to create a model in
a separate sheet (called 'model'). It then takes a copy of that sheet
(creating 'model (2)') and then renames the model with the person's name to
whom it relates (e.g. model_john').

It then repeats the process using the next column of data.

All works well for about 20 columns/models (it is always the same number)
and then I get an error 1004 at the command:

Sheets("model").Copy Befo=Sheets("INDEX")

Furthermore, if I then try to copy a sheet manually, nothing happens.
However, if I open a new book, I can copy a sheet in that and, when I return
to the problem book, I can copy sheets again.

It is Excel XP running on Windows XP. The workbook is read only. There is
no protection applied.

Ian

No luck. Even cut down the save to every 5 but without success.


Ian

"Tom Ogilvy" wrote:

Try saving the workbook every 10 sheets/Columns.

--
Regards,
Tom Ogilvy


"IanC" wrote:

I have a macro that takes data from a list and uses this to create a model in
a separate sheet (called 'model'). It then takes a copy of that sheet
(creating 'model (2)') and then renames the model with the person's name to
whom it relates (e.g. model_john').

It then repeats the process using the next column of data.

All works well for about 20 columns/models (it is always the same number)
and then I get an error 1004 at the command:

Sheets("model").Copy Befo=Sheets("INDEX")

Furthermore, if I then try to copy a sheet manually, nothing happens.
However, if I open a new book, I can copy a sheet in that and, when I return
to the problem book, I can copy sheets again.

It is Excel XP running on Windows XP. The workbook is read only. There is
no protection applied.

Ian

Might want to add the number of sheets you need, then copy the data over to
those sheets.

Previous discussions on this have alluded that the problem is the number of
copy actions rather than the number of sheets. You might create a new
workbook using a template sheet and specify it to be created with the number
of sheets you need. then copy those sheets as a group over to your workbook.
then close the dummy workbook without saving changes.

Also make sure zoom is set to 100 and avoid having objects on the sheets if
possible.

--
Regards,
Tom Ogilvy


"IanC" wrote:

No luck. Even cut down the save to every 5 but without success.


Ian

"Tom Ogilvy" wrote:

Try saving the workbook every 10 sheets/Columns.

--
Regards,
Tom Ogilvy


"IanC" wrote:

I have a macro that takes data from a list and uses this to create a model in
a separate sheet (called 'model'). It then takes a copy of that sheet
(creating 'model (2)') and then renames the model with the person's name to
whom it relates (e.g. model_john').

It then repeats the process using the next column of data.

All works well for about 20 columns/models (it is always the same number)
and then I get an error 1004 at the command:

Sheets("model").Copy Befo=Sheets("INDEX")

Furthermore, if I then try to copy a sheet manually, nothing happens.
However, if I open a new book, I can copy a sheet in that and, when I return
to the problem book, I can copy sheets again.

It is Excel XP running on Windows XP. The workbook is read only. There is
no protection applied.

Ian

Many thanks.

Reassuring to know that it is not a coding error. I will adopt your ideas
as a workaround.


Ian

"Tom Ogilvy" wrote:

Might want to add the number of sheets you need, then copy the data over to
those sheets.

Previous discussions on this have alluded that the problem is the number of
copy actions rather than the number of sheets. You might create a new
workbook using a template sheet and specify it to be created with the number
of sheets you need. then copy those sheets as a group over to your workbook.
then close the dummy workbook without saving changes.

Also make sure zoom is set to 100 and avoid having objects on the sheets if
possible.

--
Regards,
Tom Ogilvy


"IanC" wrote:

No luck. Even cut down the save to every 5 but without success.


Ian

"Tom Ogilvy" wrote:

Try saving the workbook every 10 sheets/Columns.

--
Regards,
Tom Ogilvy


"IanC" wrote:

I have a macro that takes data from a list and uses this to create a model in
a separate sheet (called 'model'). It then takes a copy of that sheet
(creating 'model (2)') and then renames the model with the person's name to
whom it relates (e.g. model_john').

It then repeats the process using the next column of data.

All works well for about 20 columns/models (it is always the same number)
and then I get an error 1004 at the command:

Sheets("model").Copy Befo=Sheets("INDEX")

Furthermore, if I then try to copy a sheet manually, nothing happens.
However, if I open a new book, I can copy a sheet in that and, when I return
to the problem book, I can copy sheets again.

It is Excel XP running on Windows XP. The workbook is read only. There is
no protection applied.

Ian