Sheet to close
 

Why won't this work?

oldjayo

Sub test()

Dim wsShtToSave As Worksheet
Set wsShtToSave = ActiveSheet
If wsShtToSave= "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End Sub

Sheet to close
 

Activesheet.name=

try this simpler version
Sub CloseifMaster()
If ActiveSheet.Name = "Master" Then
With ActiveWorkbook
'one dot only
.Save
.Close
End With
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"oldjay" wrote in message
...
Why won't this work?

oldjayo

Sub test()

Dim wsShtToSave As Worksheet
Set wsShtToSave = ActiveSheet
If wsShtToSave= "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End Sub

Sheet to close
 

If wsShtToSave.Name = "Master" Then


Gord Dibben MS Excel MVP


On Sun, 13 Dec 2009 08:30:01 -0800, oldjay
wrote:

Why won't this work?

oldjayo

Sub test()

Dim wsShtToSave As Worksheet
Set wsShtToSave = ActiveSheet
If wsShtToSave= "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End Sub

Sheet to close
 

Hi,

Why not make it simpler

If ActiveSheet.Name = "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If

Mike

"oldjay" wrote:

Why won't this work?

oldjayo

Sub test()

Dim wsShtToSave As Worksheet
Set wsShtToSave = ActiveSheet
If wsShtToSave= "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End Sub

Sheet to close
 

Thank I will use your code.
Can you tell me why mine didn't work?

"Mike H" wrote:

Hi,

Why not make it simpler

If ActiveSheet.Name = "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If

Mike

"oldjay" wrote:

Why won't this work?

oldjayo

Sub test()

Dim wsShtToSave As Worksheet
Set wsShtToSave = ActiveSheet
If wsShtToSave= "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End Sub

Sheet to close
 

You are comparing a worksheet object to a text value. Do this instead:

If wsShtToSave.Name = "Master" Then

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


"oldjay" wrote in message
...
Why won't this work?

oldjayo

Sub test()

Dim wsShtToSave As Worksheet
Set wsShtToSave = ActiveSheet
If wsShtToSave= "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End Sub

Sheet to close
 

Can you tell me why mine didn't work?

When you used the Set key word, you created an OBJECT varable. You then
tried to compare the object to a property (text string) name. That wont
work.

Object variables apply to Object items like Workbook, Sheet, Range, etc.
Regular variables apply to properties like text, values, fonts, etc.

When making comparisons, be sure they are the same data type and class.

Exmpl:

If wsShtToSave = Sheets('Master") 'compares objects

or

If wsShtToSave.Name = "Master" 'compares properties

Either of the above would work.



"oldjay" wrote in message
...
Thank I will use your code.
Can you tell me why mine didn't work?

"Mike H" wrote:

Hi,

Why not make it simpler

If ActiveSheet.Name = "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If

Mike

"oldjay" wrote:

Why won't this work?

oldjayo

Sub test()

Dim wsShtToSave As Worksheet
Set wsShtToSave = ActiveSheet
If wsShtToSave= "Master" Then
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End Sub