selecting a worksheet

[atray04]

I have tried many different way to select a worksheet in vba. Heres my code:
Set ws = tWB.Names("CC&quot.Activate
now I also tried something like this:
Set ws = tWB.Worksheets(object required).Activate
now this is where I was thrown off, because they required a index which I thought was odd that I got a odbject not defined error. I believe that this is just something simple and im somehow not doing it right so any help would be great.

 

[atray04]

well I think that I found the best way to do this, but I still have a question about it.
NewSheet.Name = "current Budget"
this is my line of code and if I had just created a new worksheet then its name would be sheet 1 so I would want to change its name. How do I reference to the new sheet object kind of like above?

 

[atray04]

ahah nevermind I figured it out

 

[xlbo]

You can't SET a variable with an action. There is a subtle distinction between a Function (Add) and an Action (Activate)

Set ws = tWB.worksheets.add

will work as ADD is a FUNCTION and the function is applied to the worksheets collection whereas

Set ws = tWB.worksheets("Sheetname&quot.activate

will not work as ACTIVATE is an ACTION.

The difference is that the .Add function CREATES a worksheet hence there is an object to reference. The ACTIVATE action does not result in an object and so cannot be used with a SET statement. Hope this clarifies the situation

What you need to use is
Set ws = tWB.Worksheets("SheetName&quot
ws.activate

Although, there is very rarely any need to activate or select unless it is as the final stage of a macro and you want to leave the cursor somewhere. See Skip's FAQ for more details faq707-4105

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[atray04]

yea, I do not need to activate the worksheet. I was just trying to get it as a object so that I can reference to it. I also found that this code works.
Set ws = tWB.Worksheets.Add
(after:=tWB.Worksheets(tWB.Worksheets.Count))
If cntws <> 1 Then
ws.Name = &quot;CC&quot;
End If
If cntws <> 2 Then
ws.Name = &quot;Dinner&quot;
End If
If cntws <> 3 Then
ws.Name = &quot;NC&quot;
End If
If cntws <> 4 Then
ws.Name = &quot;PC&quot;
End If

this way I can name my worksheets. Like I have said before I am still not to familiar with vba syntax. When I run this code and wscnt = 1 it passes right over the if then statement of if cntws <> 1 now that might not be the correct way. I know in Java you would use a == to represent equal to.

 

[atray04]

well that was interesting I guess in vba they just use a = sign in a if statement. I also change my if statements above to a nested if. thanxs for the help

 

[xlbo]

heh heh - that looks a lot like some code I posted earlier in the week ;-)

Glad you got it solved

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[atray04]

xlbo,
yea that code worked great, I just needed to make some modifications to fit what I am working on. Now I hope I will be able to have access to the files and I will not have to somehow pull them off the web oh well I will not know until monday where the files are exactly at.