Prev: duplicating data
Next: Search Directories/SubDirectories and Display in ListBox or Ms
From: kevlarmcc on 1 Apr 2010 13:19
Thanks Rick. I found one problem. The sheet named BGE actually has a space at
the end of it, so the comparison wasn't being recognized. When I add the
space in the quotes "BGE " rather than "BGE" it works. I am sorry to have
wasted everyone's time, but I have learned from everyones' help.

"Rick Rothstein" wrote:

> I **think** that you have declared "name" (with the small "n") as a variable
> somewhere where the current module can see it. I can duplicate your problem
> easily enough as follows...
>
> Dim name As String
> Sub RunAll()
> For Each Worksheet In Worksheets
> Select Case Worksheet.name
> Case "Report1"
> Case Else
> Worksheet.Activate
> If ActiveSheet.name = "BGE" _
> Then ActiveSheet.name = "Sheet999"
> End If
> End Select
> Next Worksheet
> End Sub
>
> The "n" in the property name for your Worksheet variable in the Select Case
> statement will always be lower case matching the case used in the Dim
> statement. As Dave pointed out in his response, it is a bad idea to use data
> type names, property names, or built-in function names as names for your own
> variables.
>
> --
> Rick (MVP - Excel)
>
>
>
> "kevlarmcc" <kevlarmcc(a)discussions.microsoft.com> wrote in message
> news:B8E2ADCD-38A8-4A5B-BA80-6ACB97D5EDE5(a)microsoft.com...
> > I am not sure I am explaining it correctly. It's not the value for Name it
> > is
> > the actual object Name.
> >
> > Here is the code that works:
> >
> > Sub RunAll()
> > For Each Worksheet In Worksheets
> > Select Case Worksheet.name 'This just skips the first sheet and isn't
> > crucial
> > Case "Report1"
> > Case Else
> >
> > 'Here is the issue; note name instead of Name
> > Worksheet.Activate
> > If ActiveSheet.name = "BGE" _
> > Then ActiveSheet.name = "Sheet999"
> > End Select
> > Next Worksheet
> > End Sub
> >
> > Forgive me if the misunderstanding is mine; I am new to Excel code!
> >
> >
> > "Mike H" wrote:
> >
> >> Hi,
> >>
> >> Text comparisons using = in VB are case sensitive. If you want to ignore
> >> case try this simpler version of your code
> >>
> >> Sub Rename_Shts()
> >> Dim ws As Worksheet
> >> For Each ws In ThisWorkbook.Worksheets
> >> If UCase(ws.Name) = "BGE" Then
> >> ws.Name = "Sheet999"
> >> End If
> >> Next
> >> End Sub
> >>
> >> --
> >> Mike
> >>
> >> When competing hypotheses are otherwise equal, adopt the hypothesis that
> >> introduces the fewest assumptions while still sufficiently answering the
> >> question.
> >>
> >>
> >> "kevlarmcc" wrote:
> >>
> >> > I have two copies of code that I swear are indentical except for the
> >> > captialization of an object, which seems to be automatic. Somehow one
> >> > copy of
> >> > the code doesn't auto cap the object and it is that copy that works.
> >> > Code is
> >> > below:
> >> >
> >> > Sub RunAll()
> >> >
> >> > For Each Worksheet In Worksheets
> >> > Select Case Worksheet.Name
> >> > Case "Report 1"
> >> > Case Else
> >> > Worksheet.Activate
> >> > If ActiveSheet.Name = "BGE" Then _
> >> > ActiveSheet.Name = "Sheet999"
> >> > End Select
> >> > Next Worksheet
> >> > End Sub
> >> >
> >> > When Name is capped, it doesn't work. With lowercase name it works. I
> >> > can't
> >> > seem to replicate the non-capped object because when writing new code
> >> > it
> >> > won't let me not capitalize it. I am not sure what I did to get the
> >> > object
> >> > not capitalized in the first place. Anyone understand this?
>
> .
>
First  |  Prev  | 
Pages: 1 2 3
Prev: duplicating data
Next: Search Directories/SubDirectories and Display in ListBox or Ms