using a value from the sheet to the left of the active sheet

I have a sub that adds a new sheet then copies a template into the sheet and opens a user form that the user enters details of a sales order into. When the user submits the form the details are passed to the new sheet and then the sheet is renamed to whatever the order number is, which should be sequential 1,2,3 etc

Is there a way for my user form to pick up the order number value from the previous sheet (remembering that I can't specify the name or number to refer to, only that it will be immediately to the left of the active sheet) and add 1 to that number and show that in the text box 'orderNo' , and then pass the new value to the new sheet on submit?

If I could do this it would prevent my users from messing up the order number sequence.

Hi vn900,

The answer is YES!!

Sheets are indexed from left to right from 1 (the very left one) to the farthest on the right.
You can do VBA code of.
Activesheet.Index which will return the index number of the sheet you are looking at.
This all means if you want to get somehing off the sheet to the immediate left you would use

Please Login or Register  to view this content.

So say you want to copy range A1 to E5 from the sheet to the left to the active sheet. Your code would look like

Please Login or Register  to view this content.

BTW - the last worksheet number is given by

Please Login or Register  to view this content.

hope all this brain drain helps.