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

daveus

Board Regular
Joined
May 18, 2004
Messages
191
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:
Code:
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!
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Are you just trying to loop through all the sheets?

If you are you could use something like this.
Code:
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

Board Regular
Joined
May 18, 2004
Messages
191
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 18, 2004
Messages
191

ADVERTISEMENT

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-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Well if it is exactly as you stated.
Code:
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

Board Regular
Joined
May 18, 2004
Messages
191
that's great. thanks! i appreciate it.

you guys are too smart...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,461
Messages
5,596,273
Members
414,049
Latest member
MisterExcel26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top