Macro don’t work

[K]

Hi all, I have macro (see below) which basically check for sheet name
if it already exists and if not then it copy the specified sheet to
last and name it. But I am getting error in macro below on line "If
ActiveWorkbook.Worksheets(Me.Range("B12").Value) Is Nothing Then".
Can please any friend tell what am i doing wrong.

Private Sub CommandButton1_Click()
If Me.Range("B12").Value = "" Then
MsgBox "ENTER FURTHER OBJECTIVE CODE", vbCritical, "NO FUROBJ ENTERED"
Else
If ActiveWorkbook.Worksheets(Me.Range("B12").Value) Is Nothing Then
Sheets("Exp & Inc Template").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = Me.Range("B12").Value
ActiveSheet.Range("L1").Value = Me.Range("B12").Value
ActiveSheet.Range("A32").Select
Me.Range("B12:E17").ClearContents
Else
MsgBox "SHEET" & "* " & Me.Range("B12").Value & " *" & "ALREADY
EXIST", vbCritical, "ERROR"
End If
End If
End Sub

 

[Bob Phillips]

Private Sub CommandButton1_Click()
Dim FileLen As Long

If Me.Range("B12").Value = "" Then

MsgBox "ENTER FURTHER OBJECTIVE CODE", vbCritical, "NO FUROBJ
ENTERED"
Else

On Error Resume Next
FileLen = Len(ActiveWorkbook.Worksheets(Me.Range("B12").Value).Name)
On Error GoTo 0
If FileLen = 0 Then
Sheets("Exp & Inc Template").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = Me.Range("B12").Value
ActiveSheet.Range("L1").Value = Me.Range("B12").Value
ActiveSheet.Range("A32").Select
Me.Range("B12:E17").ClearContents
Else
MsgBox "SHEET" & "* " & Me.Range("B12").Value & " *" & "ALREADY
EXIST", vbCritical, "ERROR"
End If
End If
End Sub

 

[K]

Private Sub CommandButton1_Click()
Dim FileLen As Long

    If Me.Range("B12").Value = "" Then

        MsgBox "ENTER FURTHER OBJECTIVE CODE", vbCritical, "NO FUROBJ
ENTERED"
    Else

        On Error Resume Next
        FileLen = Len(ActiveWorkbook.Worksheets(Me.Range("B12")..Value).Name)
        On Error GoTo 0
        If FileLen = 0 Then
            Sheets("Exp & Inc Template").Copy after:=Sheets(Sheets.Count)
            ActiveSheet.Name = Me.Range("B12").Value
            ActiveSheet.Range("L1").Value = Me.Range("B12")..Value
            ActiveSheet.Range("A32").Select
            Me.Range("B12:E17").ClearContents
        Else
            MsgBox "SHEET" & "* " & Me.Range("B12").Value & "*" & "ALREADY
EXIST", vbCritical, "ERROR"
        End If
    End If
End Sub

--
__________________________________
HTH

Bob







- Show quoted text -

Thanks lot Bob

 

[Chip Pearson]

Try code like the following:

Sub AAA()
Dim WB As Workbook
Dim WS As Worksheet
Dim WSName As String
Set WB = ActiveWorkbook
WSName = Range("B12").Value
If WSName = vbNullString Then
MsgBox "Enter a sheet name in B12"
Exit Sub
End If
On Error Resume Next
Set WS = WB.Worksheets(WSName)
On Error GoTo 0
If WS Is Nothing Then
' sheet does not exist. create it
With WB.Worksheets
Set WS = .Add(after:=.Item(.Count))
End With
WS.Name = WSName
Else
' sheet already exists.
MsgBox "Sheet '" & WSName & "' already exists."
End If
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[K]

Try code like the following:

Sub AAA()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim WSName As String
    Set WB = ActiveWorkbook
    WSName = Range("B12").Value
    If WSName = vbNullString Then
        MsgBox "Enter a sheet name in B12"
        Exit Sub
    End If
    On Error Resume Next
    Set WS = WB.Worksheets(WSName)
    On Error GoTo 0
    If WS Is Nothing Then
        ' sheet does not exist. create it
        With WB.Worksheets
            Set WS = .Add(after:=.Item(.Count))
        End With
        WS.Name = WSName
    Else
        ' sheet already exists.
        MsgBox "Sheet '" & WSName & "' already exists."
    End If
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)




- Show quoted text -

thanks chip it works greate