i want to use a loop with sheet codenames. i set a counter to determine the
which sheets will be selected. how can i use this counter to add to the
sheet property to create a sheetname?

if the counter is 2, i want to select sheet2. i have tried things kike this,
but can't figure it out

Sheets("sheet" & Str(cntE)).Select

can someone tell me what i am missing?

--


Gary

Hi Gary,

I think this should do the trick, i have had several problems when
selecting sheets and passing too much info into the mix so I just
seperate it a little.

testCntE = "Sheet" & CntE
Sheets(testCntE).Select

Any problems give me a shout.

James

james, doesn't seem to work here. get a subscript out of range error. when i
put the cursor on the variable in Sheets(testCntE).Select, i get "Sheet3".
but always get the debugger.

i have the sheet tabs renamed, and i was looking for a way to select the
sheet by sheet number. renaming the tabs shouldn't have any affect, should
they?

--


Gary


wrote in message
oups.com...
Hi Gary,

I think this should do the trick, i have had several problems when
selecting sheets and passing too much info into the mix so I just
seperate it a little.

testCntE = "Sheet" & CntE
Sheets(testCntE).Select

Any problems give me a shout.

James

Hi Gary,

Yeah when you use the Sheets(" ") notation like so it is in effect
asking for the tab name. When you want to select Sheet2 do you mean
where it says in the VBE Sheet2 or do you mean the second tab?

For the tab location, just use:
Dim CntE as Integer
Sheets(CntE).select

When selecting sheets by code name you just use
Sheet1.select 'no reference to the worksheets collection
Although I have had problems with how to select it using your counter.

James

ok, thanks for explaining that. i have tried appending the counter value and
had no luck.

i have it working, it's just if i move sheets to the beginning, i have to
edit code to get the correct sheets selected. i am just using the sheet
reference location, for example, the 3rd sheet in the workbook, but if i
move a sheet in front, it is now the 4th sheet in the workbook and i have to
change a variable to get the correct sheet. i was looking for a way so it
didn't matter where the sheets were. they are listed in vb as sheet2 thru
sheet13, so i thought i would try that.

these are monthly sheets, jan thru dec. where i run into a problem is, i go
to the monthly sheet but always return to the summary sheet to paste and add
the results.
in the loop the activesheet always has to change..

that's why i am trying to use the sheet and number scheme. if i can't, it's
not a big deal, like i said, it works ok right now.


thanks for helping
--


Gary


wrote in message
oups.com...
Hi Gary,

Yeah when you use the Sheets(" ") notation like so it is in effect
asking for the tab name. When you want to select Sheet2 do you mean
where it says in the VBE Sheet2 or do you mean the second tab?

For the tab location, just use:
Dim CntE as Integer
Sheets(CntE).select

When selecting sheets by code name you just use
Sheet1.select 'no reference to the worksheets collection
Although I have had problems with how to select it using your counter.

James

Hi Gary,

Ah, so you are trying to combine the sheets into a summary? A different
approach could be to set the summary sheet, this will work as long as
you don't rename the summary sheet.

Dim xsheet As Worksheet
Dim Sumry As Worksheet
Set Sumry = ThisWorkbook.Sheets("Summary")
Application.ScreenUpdating = False
For Each xsheet In ThisWorkbook.Worksheets
If xsheet.Name = "Summary" Then GoTo Nextxsheet
'code for copying info
Sumry.Select
'paste results
xsheet.Select
'go back for other results
'etc.



Nextxsheet:
Next xsheet
This will then loop through all sheets except the summary sheet.

Are we getting there?

Cheers

James

Hi,

Sheets("Sheet" & cntE).Select

or

Sheets("sheet" & Trim(Str(ctnE))).Select

Using the first avoids the problem of STR adding an extraneous blank at the
end of the string and hence the need to use TRIM to remove it.

HTH

"Gary Keramidas" wrote:

i want to use a loop with sheet codenames. i set a counter to determine the
which sheets will be selected. how can i use this counter to add to the
sheet property to create a sheetname?

if the counter is 2, i want to select sheet2. i have tried things kike this,
but can't figure it out

Sheets("sheet" & Str(cntE)).Select

can someone tell me what i am missing?

--


Gary