I want to set the variable ws similar to the For statement, but I want to set it for just the current active sheet.

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

please help this is not working:

ws = ActiveSheets

Activesheets does not exist. Activesheet does.
I assumed it's just a typo in your post... but typo's in code can be
crucial!

Therefor: ALWAYS start your code modules with
OPTION EXPLICIT
(it will force you to declare your variables before you can use them,
it may look cumbersome... but it's just good practice)


dim str as string
dim wks as worksheet 'this is an OBJECT variable
dim var 'type is not declared = variant


str = "Hi" 'Assign to normal variable officially it's
LET str = "Hi" 'but you're allowed to skip the LET statement

SET wks = Activesheet 'Assign to object variable

'Anytinh can be assinged to a variant but if you're working with an
object you must use SET
var = "Hi"
set var = Activesheet



Same goes for comparing...

use IS for objects

if wks IS nothing
if wks IS thisworkbook.worksheets(1)

use = for values
if str = "hi"
if str = "oops"
if wks.name = "Sheet1"

etc...

search help on DIM and take it from there.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?U2ltb24gU2hhdw==?="
wrote:

I want to set the variable ws similar to the For statement, but I want
to set it for just the current active sheet.

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

please help this is not working:

ws = ActiveSheets

Simon,

There is only one activesheet in a workbook, so you can get it simply by

Set ws = Activesheet

but you could just as easily use use ACtivesheet in your code directly.

You may be thinking of selected sheets, which is accessed like

For Each ws In Activewindow.SelectedSheets
...
Next ws

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Simon Shaw" wrote in message
...
I want to set the variable ws similar to the For statement, but I want to
set it for just the current active sheet.

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

please help this is not working:

ws = ActiveSheets

Thanks, it was the SET command I was missing, I had tried a million different ways otherwise (without an 's', with worksheets, etc...)

Just to add to Bob's reply...

One very nice reason to use the variable ws (and to declare it as a worksheet)
is that you'll get VBE's intellisense while you're writing the code.



Bob Phillips wrote:

Simon,

There is only one activesheet in a workbook, so you can get it simply by

Set ws = Activesheet

but you could just as easily use use ACtivesheet in your code directly.

You may be thinking of selected sheets, which is accessed like

For Each ws In Activewindow.SelectedSheets
...
Next ws

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Simon Shaw" wrote in message
...
I want to set the variable ws similar to the For statement, but I want to
set it for just the current active sheet.

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

please help this is not working:

ws = ActiveSheets


--

Dave Peterson