By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,118 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,118 IT Pros & Developers. It's quick & easy.

Select Case worksheet

100+
P: 142
I want to use the Select Case statement in excel VBA to check each worksheet. My code is something like this:
Expand|Select|Wrap|Line Numbers
  1. Sub Template()
  2. n=1
  3. Do While n>0
  4. Select Case Worksheets("Template(n)")
  5. Case "Template(n)"
  6. Worksheets("xxx")=Worksheets("Template(n)")
  7. Msgbox("set to same")
  8. End select
  9. n=n+1
  10. Loop
  11. End Sub
  12.  
Anybody know what is wrong with my code?

thanks
Apr 26 '07 #1
Share this Question
Share on Google+
5 Replies


SammyB
Expert 100+
P: 807
Expand|Select|Wrap|Line Numbers
  1. Sub Template()
  2.     n = 1
  3.     Do While n > 0
  4.         Select Case Worksheets("Template(n)")
  5.         Case "Template(n)"
  6.             Worksheets("xxx") = Worksheets("Template(n)")
  7.             MsgBox ("set to same")
  8.         End Select
  9.         n = n + 1
  10.     Loop
  11. 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?
Apr 26 '07 #2

Expert 5K+
P: 8,434
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)...
Expand|Select|Wrap|Line Numbers
  1. Sub aaa()
  2. Dim sht As Worksheet
  3. For Each sht In Application.Worksheets
  4.   Select Case sht.Name
  5.     Case "Sheet1"
  6.       sht.Cells(1, 1) = "This is the first sheet"
  7.     Case "Sheet2"
  8.       sht.Cells(1, 1) = "This is the second sheet"
  9.     Case "Sheet3"
  10.       sht.Cells(1, 1) = "This is the third sheet"
  11.   End Select
  12. Next
  13. End Sub
Apr 26 '07 #3

SammyB
Expert 100+
P: 807
Also, to copy a worksheet, you need to
Expand|Select|Wrap|Line Numbers
  1.  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:
Expand|Select|Wrap|Line Numbers
  1. Worksheets("Sheet1").Copy After:=Worksheets(3)
HTH --Sam
Apr 27 '07 #4

100+
P: 142
Also, to copy a worksheet, you need to
Expand|Select|Wrap|Line Numbers
  1.  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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 30 '07 #5

Expert 5K+
P: 8,434
Excellent! :)

Glad to see you've got the result you wanted.
Apr 30 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.