Error 1004

[neilshaw]

Following on from http://www.excelforum.com/excel-prog...ml#post2521369

I am experiencing an error and cannot work out why.

Any ideas?

[TMS]

The error is on this line of code:

Please Login or Register  to view this content.

That's Sheet Data, Column C, Row 3 which has "INPUT" in it. Unfortunately, you already have a sheet called "INPUT", hence the error.

It may be because every cell in that column has "INPUT" in it.


Regards

[neilshaw]

I'm confused, the sheet was working fine until I put in the extra code that Jerry suggested in the other thread. The piece of code you refer to ran smoothly. Any more suggestions?

[TMS]

I'm not sure how I can say it differently. If you choose to create the new sheets in the current workbook, the code tries to copy the template and rename it as INPUT. There is already a sheet in the workbook supplied with the name INPUT, so the code breaks.

Have you tried tracking the code? Not sure that would make a difference though because, whichever row is selected, it will find the worksheet name INPUT.

Regards

[TMS]

Maybe you could change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

This would ensure the INPUT form related to the Scheme number.

Just a thought.

Regards

[neilshaw]

You state:-

There is already a sheet in the workbook supplied with the name INPUT

However, there is not a sheet called INPUT in the workbook supplied. The sheet gets renamed INPUT and this only happens after the code is executed. Perhaps it would help if you saw how the code worked before:-

Please Login or Register  to view this content.

If you paste over the code in the spreadsheet, it runs fine and all of the references to the INPUT sheet do not kick up a fuss...

[neilshaw]

In response to your previous message, it is imperative that the sheet is named INPUT because eventually there will a dozens of formulas making reference to this sheet when I copy in all of the other sheets, it would take hours to change all of the vlookups.

[TMS]

My apologies; you are right, there is not a sheet called INPUT in the workbook as supplied.

However, the code loops through the rows on the Data sheet starting at 2 and going to the last row (4). On the first pass, it copies the Template which becomes Template (2) and is renamed INPUT because that is in cell C2 on the Data sheet. On the second pass, it copies the Template which becomes Template (2) and then tries to rename it INPUT because that is in cell C3 on the Data sheet. This fails because you already named a sheet INPUT on the first pass and you can't have two sheets with the same name. That's what the error 1004 is telling you.

As I said, I don't know how to say it differently. I understand how the current code works because I have stepped though it.

If you choose to create separate workbooks for each Input form, the code works correctly and creates three workbooks in a folder of choice.

Regards

[royUK]

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.