Macro don't work
On Feb 12, 10:17*am, "Bob Phillips" wrote:
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")..Val ue).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
"K" wrote in message
...
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- Hide quoted text -
- Show quoted text -
Thanks lot Bob
|