Insert worksheet code error

[funkmonsteruk]

I have a userform attached to an Excel workbook which allows the user to open another workbook and insert its single page. It was working fine until two days ago when i started getting a "run-time error '9' subscript out of range" error message.

The code breaks down when i try and insert the worksheet from the newly opened workbook into the workbook with the form.

This is the code i'm using for this part of the operation, and it's breaking on the first line:

lastsheet = Workbooks("Estate-taps-weekly&quot.Sheets.Count
Sheets(1).Move After:=Workbooks("Estate-taps-weekly&quot.Sheets(lastsheet)

Does anybody have any suggestions?

 

[xlbo]

If this is run a lot, have a look at the CODE names related to the worksheets
You can see these in the VBE properties window
I'm betting one of the names is Sheet11111111111111
You are only allowed a certain length of sheetname and when you do a move/copy sheet, the new one has an extra digit on the end - this may well be causing the error Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[combo]

As the code was working, I would first check if the workbook "Estate-taps-weekly" is open, and if it is still its proper name.

combo

 

[RobBroekhuis]

That's an interesting point, Geoff! One I hadn't come across. I tested this in my XL2000, and found that the code names it creates are "Sheet1 (2)", "Sheet1 (3)", and so on. Is it XL97 where the "1" gets appended?
Rob

 

[xlbo]

Rob - based on xl97 (I don't have any other versions to test on)
If you copy a sheet (Sheet1) once, you get
Sheet12 (Sheet1 (2) - this is in the project window
If you copy the same sheet again, you get
Sheet13 (Sheet1 (3)
If you do this 50 times, you will get
Sheet150 (Sheet1 (51)
Obviously, this would take a looooong time to increment enough to get to the character limit on the name

HOWEVER, if you copy the COPIED sheet each time, you get:

Sheet1 (Sheet1 (1)
Sheet11 (Sheet1 (2)
Sheet111 (Sheet1 (3)
Sheet1111 (Sheet1 (4)
etc etc etc
and I could only do thios apprx 30 times before it errored out Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[RobBroekhuis]

This must have been fixed in XL2000. I can't get anything besides "Sheet1", "Sheet2", ... as the object code name, no matter where I copy from.
Rob

 

[xlbo]

Rob - try running these 2 pieces of code in a new wb and let me know the results (if you would be so kind)

Sub CopyOriginal()
for i = 1 to 30
sheets("Sheet1&quot.copy before:=sheets(1)
next i
end sub

sub copyTheCopy()
for i = 1 to 30
sheets(1).copy before:=sheets(1)
next i
end sub Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[RobBroekhuis]

Geoff,
I gave it a try, in a workbook that started out with just one sheet. I first ran the first sub. Then I ran the second sub twice. I ended up with 91 sheets, numbered from Sheet1 to Sheet91 in the project explorer, and named "Sheet1 (1)" through "Sheet1 (91)".

Rob

 

[xlbo]

Well - does indeed look like this is a '97 problem then
Cheers for enlightening me Rob
Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[funkmonsteruk]

Thanks for all the responses - The sheets in question have names before they are inserted - they all use a similar naming protocol - i have looked in the project window and they are named sheet1(name), sheet2(name) etc...

If it's relevant i can still insert the sheet manually, but the code simply breaks down.

I have three workbooks all using the same code and they all stopped working on the same day, which i thought was a bit strange.

When i check the values for the line of code it tells me that lastsheet is empty and Workbooks("Estate-taps-weekly&quot.Sheets.Count is out of range......

 

[combo]

Hi funkmonsteruk,
that means that the workbook "Estate-taps-weekly" is not open. Someone could have changed a letter, so in your code you refer to workbook, which does not exist.

combo

 

[RobBroekhuis]

Is Estate-taps-weekly a .xls file? If not, you need to specify the whole filename, including .xlt, .txt, etc.
Rob

 

[funkmonsteruk]

Cheers Rob,

The workbook was a .xls workbook, but i inserted the full file name anyway (Estate-taps-weekly.xls) and it is now working - cheers for all the help lads.

 

[RobBroekhuis]

Yes, my error. Even if it's a .xls you need to specify the extension.
Rob

 

[funkmonsteruk]

Just a quick one - how do you get those little smiley faces and other miinature comedy icons to appear at the bottom of your post - I've got animated icon envy.....

 

[RobBroekhuis]

Click on the Emoticons/Smileys link just above the "preview post" button. And in your user profile, you can set a signature, which can include smileys.
Rob

 

[funkmonsteruk]

Cheers

 

[funkmonsteruk]