Sheet to close

  • Thread starter Thread starter oldjay
  • Start date Start date
O

oldjay

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
 
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
 
Hi,

Why not make it simpler

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

Mike
 
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
 
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.
 
Back
Top