Find Worksheet
 

Seems to be quite a simple one. But I can't figure it out.

Basically I've got a macro that saves a worksheet each month and renames it.
All works fine, but as this is a shared workbook I want to put a failsafe in
to make sure the macro to update isn't pressed twice thus trying to create a
new sheet and save as the name. (because if this happens the macro crashes
(because it can't rename the same).

So I wasn to run a simple If function which searches a workbook for a name
if it doesn't exist carry on with the code, if it does exist, then it will
create a messagebox.


Something like this but it needs to search the Sheet names not a range.
sub searchmonth ()

Cmonth = ("M1") ' Inthis case February

Set searchrange = workbooks ("export.xls")

Set test = searchrange.find (what= Cmonth)

If test Is Nothing Then

' it runs my code

Else

'MsgBox

End If
End sub


Thanks for any help!

Carl

Find Worksheet
 

sub searchmonth ()

Cmonth = ("M1") ' Inthis case February

Set export_wbk = workbooks ("export.xls")
Found = false
for each sht in export_wbk.sheets
if sht.name = Cmonth then
Found = True
exit for
end if
next sht

If Found = False Then

' it runs my code

Else

'MsgBox

End If
End sub



"Carlos" wrote:

Seems to be quite a simple one. But I can't figure it out.

Basically I've got a macro that saves a worksheet each month and renames it.
All works fine, but as this is a shared workbook I want to put a failsafe in
to make sure the macro to update isn't pressed twice thus trying to create a
new sheet and save as the name. (because if this happens the macro crashes
(because it can't rename the same).

So I wasn to run a simple If function which searches a workbook for a name
if it doesn't exist carry on with the code, if it does exist, then it will
create a messagebox.


Something like this but it needs to search the Sheet names not a range.
sub searchmonth ()

Cmonth = ("M1") ' Inthis case February

Set searchrange = workbooks ("export.xls")

Set test = searchrange.find (what= Cmonth)

If test Is Nothing Then

' it runs my code

Else

'MsgBox

End If
End sub


Thanks for any help!

Carl

Find Worksheet
 

Joel,

Many thanks again!! Works perfectly!!

Cheers for your time

Carl

"Joel" wrote:

sub searchmonth ()

Cmonth = ("M1") ' Inthis case February

Set export_wbk = workbooks ("export.xls")
Found = false
for each sht in export_wbk.sheets
if sht.name = Cmonth then
Found = True
exit for
end if
next sht

If Found = False Then

' it runs my code

Else

'MsgBox

End If
End sub



"Carlos" wrote:

Seems to be quite a simple one. But I can't figure it out.

Basically I've got a macro that saves a worksheet each month and renames it.
All works fine, but as this is a shared workbook I want to put a failsafe in
to make sure the macro to update isn't pressed twice thus trying to create a
new sheet and save as the name. (because if this happens the macro crashes
(because it can't rename the same).

So I wasn to run a simple If function which searches a workbook for a name
if it doesn't exist carry on with the code, if it does exist, then it will
create a messagebox.


Something like this but it needs to search the Sheet names not a range.
sub searchmonth ()

Cmonth = ("M1") ' Inthis case February

Set searchrange = workbooks ("export.xls")

Set test = searchrange.find (what= Cmonth)

If test Is Nothing Then

' it runs my code

Else

'MsgBox

End If
End sub


Thanks for any help!

Carl

Find Worksheet
 

Sub gotosheet()
Application.Goto Sheets(Range("m1").Value).Range("a1")
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlos" wrote in message
...
Seems to be quite a simple one. But I can't figure it out.

Basically I've got a macro that saves a worksheet each month and renames
it.
All works fine, but as this is a shared workbook I want to put a failsafe
in
to make sure the macro to update isn't pressed twice thus trying to create
a
new sheet and save as the name. (because if this happens the macro crashes
(because it can't rename the same).

So I wasn to run a simple If function which searches a workbook for a
name
if it doesn't exist carry on with the code, if it does exist, then it will
create a messagebox.


Something like this but it needs to search the Sheet names not a range.
sub searchmonth ()

Cmonth = ("M1") ' Inthis case February

Set searchrange = workbooks ("export.xls")

Set test = searchrange.find (what= Cmonth)

If test Is Nothing Then

' it runs my code

Else

'MsgBox

End If
End sub


Thanks for any help!

Carl