Basic Loop Trouble - need to convert active sheet to integer

[mick86]

I'm new to usign loops and I've run into a bit of trouble, I need to use a loop to add a value of 1 to the workseets name on each repetition. The worksheets are all named with single numbers but I'm not sure how to convet the active sheet reference into an integer t hat I can use to add a value to. I also tried

Please Login or Register  to view this content.

I also tried

Please Login or Register  to view this content.

I also tried

Please Login or Register  to view this content.

Any assistance greatly appreciated

Thanks

[xladept]

Hi Mick,

If it's the name - you've got to use quote marks, because the name is a string - when you just use a number, that's the sheet index.

Please Login or Register  to view this content.

or something similar!

[mick86]

I tried like this but it gets stuck on Sheet "2"

Please Login or Register  to view this content.

[xladept]

Please Login or Register  to view this content.

It all depends on how your sheets are named - this will give you Sheets(2) to Sheets(21)

If you want "Sheet2" to "Sheet21" then you could use:

Please Login or Register  to view this content.

[mick86]

So much simpler, I was perhaps coming at things from the wrong direction.

My sheet names do not contain the word "sheet', they just have numbers.

Your code almost works, though the fact that the 1st sheet i created is called "index" seems to be a problem. The reference style you suggested seems to refer to the sheets number derived from its creation time rather than the sheets name.

As you write it it starts on the sheet named "1" which is (sheet 2) according to the visual basic menu and it runs up until sheet "20" (which is technically sheet 19).

If I change the code to:

Please Login or Register  to view this content.

This is a work around for the problem. Perhaps if quotation marks were included with in the brackets I'd be able to refer to the exact sheet name

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

I'll be able to use that in multiple locations where I had previously been doing things manually.

[xladept]

The "i" wont work.

[mick86]

Neither way seems to work

Please Login or Register  to view this content.

This doesn't work either

[xladept]

How about a mockup? OR:

Please Login or Register  to view this content.

[mick86]

Attached a mock up, it works as is but refers to the sheets index numbers rather than the sheets names. I was initially hoping to do it by sheet name. That said it works alright as it is.

[mick86]

How about a mockup? OR:

Please Login or Register  to view this content.

This is a good option that works around needing to name the involved worksheets.