Select Case worksheet

[joemo2003]

I want to use the Select Case statement in excel VBA to check each worksheet. My code is something like this:

Sub Template()
n=1
Do While n>0
Select Case Worksheets("Template(n)")
Case "Template(n)"
Worksheets("xxx")=Worksheets("Template(n)")
Msgbox("set to same")
End select
n=n+1
Loop
End Sub
 

Anybody know what is wrong with my code?

thanks

[SammyB]

Sub Template()
    n = 1
    Do While n > 0
        Select Case Worksheets("Template(n)")
        Case "Template(n)"
            Worksheets("xxx") = Worksheets("Template(n)")
            MsgBox ("set to same")
        End Select
        n = n + 1
    Loop
End Sub

Do While n > 0
This will loop from 1 to infinity: it will take a long time.

Select Case Worksheets("Template(n)")
Case "Template(n)"
This is equivalent to an If clause:
If the worksheet whose name is Template(n) is equal to the string Template(n)
Note the n is just the letter n, it has nothing to do with the loop variable; ie, it will be the letter n for each iteration of the loop.
However, a worksheet is never going to equal a string, so the Select Case will always fail. Fortunately, it will fail with "Object doesn't support this property or method."

Worksheets("xxx") = Worksheets("Template(n)")
Sounds like a nice way to copy a worksheet, but it won't work, you will get "Object doesn't support this property or method."

So, your code does nothing forever. :D What do you want it to do?

[Killer42]

If you just want to loop through the worksheets, your best bet is probably a For Each loop. You can still throw a Select Case in there if you insist. For example, here's a little macro I just tested (it worked)...

Sub aaa()
Dim sht As Worksheet
For Each sht In Application.Worksheets
  Select Case sht.Name
    Case "Sheet1"
      sht.Cells(1, 1) = "This is the first sheet"
    Case "Sheet2"
      sht.Cells(1, 1) = "This is the second sheet"
    Case "Sheet3"
      sht.Cells(1, 1) = "This is the third sheet"
  End Select
Next
End Sub

[SammyB]

Also, to copy a worksheet, you need to

 Worksheets("Sheet1").Cells.Copy Sheets("Sheet2").Cells

But, that does not copy any embedded objects like drawings, charts, or controls. To copy them, you must create a new worksheet:

Worksheets("Sheet1").Copy After:=Worksheets(3)

HTH --Sam

[joemo2003]

Also, to copy a worksheet, you need to

 Worksheets("Sheet1").Cells.Copy Sheets("Sheet2").Cells

But, that does not copy any embedded objects like drawings, charts, or controls. To copy them, you must create a new worksheet:

Worksheets("Sheet1").Copy After:=Worksheets(3)

HTH --Sam

i was thinking too complicated at begining, what i want just to see if that sheet exist, so I just use
For each sh in thisworkbook.sheets
if sh.name="xxx" then

thanks anyway.

[Killer42]

Excellent! :)

Glad to see you've got the result you wanted.