Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro don’t work
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 557
Default Macro don’t work
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default Macro don't work
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").Valu e).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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 557
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default Macro don’t work
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)


On Thu, 12 Feb 2009 01:59:17 -0800 (PST), K
wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 557
Default Macro don’t work
On Feb 12, 3:16*pm, Chip Pearson wrote:
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)

On Thu, 12 Feb 2009 01:59:17 -0800 (PST), K
wrote:



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 chip it works greate
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to update a column in a work based on another work sheet WickerMan New Users to Excel 1 December 4th 09 12:58 PM
Macro works Macro does not work Wanna Learn Excel Discussion (Misc queries) 4 March 24th 08 12:51 PM
Why Won't This Macro Work? Pausert of Nikkeldepaiin Excel Discussion (Misc queries) 7 February 4th 08 01:05 PM
If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? Marc Excel Programming 2 July 12th 06 04:10 AM
macro that doesn't work... VILLABILLA[_3_] Excel Programming 1 February 27th 04 11:39 AM


All times are GMT +1. The time now is 07:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement