reference sheet (e.g. sheet1 format) in vba

[daveus]

Hi,

there are a ton of ways to reference a sheet in vba, i'm partial to the sheet1, sheet2, etc. avoiding the name of the sheet. sheet(3) seems to return the third sheet in order of the workbook. I would like to reference with a for loop the sheet1 format. I tried sheeti, sheet i, etc. but couldnt' get something to work. I want it to be consistent for the sheet number assinged that we see in the vba editor and not relative to the sheet location or name assigned, but the hard 'name' assigned that doesn't change. Any thoughts how i would do that?

here is my sample code for looping through 15 sheets identified as Sheet1 through 15:

For i = 1 To 15
    Sheet ?????.Select  '<-here is where i'm not sure how to reference how i'd like as described above
    Cells(1, 1).value = "testing" & i    
Next i
end sub

I hope this makes sense. Any thoughts would be greatly appreciated. Thanks all!

 

[Norie]

Are you just trying to loop through all the sheets?

If you are you could use something like this.

For Each ws In Worksheets.

       ws.Cells(1,1).Value = "Testing " & ws.Index

Next ws

If it's not all the sheets this code can easily be added to to ignore particular sheets based on some criteria. eg CodeName

 

[daveus]

no, it is not all the sheets in the workbook. These are the only ones that will have changing data. others are summary sheets, etc.

please elaborate on methods. thanks!

 

[Norie]

Can you elaborate on exactly what you want to do?

Which sheets should be ignored?

How can they be identified?

You mention changing data?

Are you using worksheet event code? eg Change

 

[daveus]

i thought i did in the first post. I only want to deal with sheet1 to sheet15. the sheets could be named anything for this example.

I want it to loop through the sheet1 to sheet15 and perform a function. i included a simple example above.

They are identified by the sheet # assigned. (e.g. sheet1, sheet3, etc.). sheet1 could be named 'nothing' and sheet3 could be named 'something'. I want to reference the sheet# for this.

i'm using code almost just like i show in the above, just simplified the procedure on each page.

Let me know if you need more clarification.

 

[Norie]

Well if it is exactly as you stated.

For Each ws In Worksheets
      Select Case Mid(ws.CodeName, 6)
           Case 1 To 15
                ws.Cells(1, 1) = "Testing " & ws.Index
           Case Else
                ' do nothing
       End Select
Next ws

 

[daveus]

that's great. thanks! i appreciate it.

you guys are too smart...