Error handling 1004 for creating new sheets

Microsoft Excel 2003 (full product)
November 23, 2010 at 04:58:11
Specs: Windows XP
I've done Google search after Google search on this but can't find the specific code to help so I figured I'd ask here as I’ve gotten such great help in the past.

I created a macro in excel that will create a new spreadsheet from a master sheet called Fresh. It then changes the name to match the date it was created. Here is the code I used.

'Copy from the master copy

Sheets("Fresh").Select
Sheets("Fresh").Copy After:=Sheets(2)
ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = Month(Date) & "-" & Day(Date) & "-" & Year(Date)

'Set the current Date
Range("A2").Select

ActiveCell.FormulaR1C1 = Now

First question I have is if the operator clicks the button twice it gets the error message to pop up. I tried to use this code I found on the internet. But of course it isn't working for me.

'Copy from the master copy

Sheets("Fresh").Select
Sheets("Fresh").Copy After:=Sheets(2)
ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = Month(Date) & "-" & Day(Date) & "-" & Year(Date)
On Error GoTo Changedate_Err:
'Set the current Date
Range("A2").Select

ActiveCell.FormulaR1C1 = Now



End Sub

Sub Changedate_Err():

Select Case Err.Number

Case 1004 ' If user answers no to the dialog to open the file read-only
'just exit sub or display a warning to the user.
MsgBox "The workbook you want to open is already opened. Please try again.", vbCritical, Application.Name
Resume Changedate_Exit

Case Else
' In case of any other error.
MsgBox Err.Number & " " & Err.Source & " " & Err.Description, vbCritical, Application.Name
Resume Changedate_Exit

End Select

End Sub


I get a label not defined on the On Error GoTo Changedate_Err:
What I’m looking for is something to tell the operator that a spreadsheet for that day is already made and they don't need to create a new one. At the same time I don't want a new one created as they are named Fresh(2).

On a side note is there a way for when I name the sheet to the current day to use only the last 2 digits of the year?

Thanks for any help in this matter.


See More: Error handling 1004 for creating new sheets

Report •

#1
November 23, 2010 at 05:15:31
Bah I figured out the first part of it I got the error handling to work. In case anyone was wondering first thing I forgot to do was declare Changedate_Err as a label at first I dimed it as integer. Heres the new code that I got to work.

Dim Changedate_Err as Label
'Copy from the master copy
On Error GoTo Changedate_Err
Sheets("Fresh").Select
Sheets("Fresh").Copy After:=Sheets(2)

ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = Month(Date) & "-" & Day(Date) & "-" & Year(Date)


'Set the current Date
Range("A2").Select

ActiveCell.FormulaR1C1 = Now

Changedate_Err:

Select Case Err.Number

Case 1004 ' If user answers no to the dialog to open the file read-only
'just exit sub or display a warning to the user.
MsgBox "The chart for today is already created. Select Yes to delete the duplicate on the next message box.", vbCritical, Application.Name
ActiveSheet.Delete

Case Else
' In case of any other error.
MsgBox Err.Number & " " & Err.Source & " " & Err.Description, vbCritical, Application.Name

End Select

Is there a way to auto delete the file instead of just the Activesheet.delete it still pops up a window and I'm not sure all operators will click the delete button. And then just the two year date thing.


Report •

#2
November 23, 2010 at 05:33:24
First:

Please read the How To referenced in my signature line before posting code to this forum. By following the instructions found there, your code will be a lot easier for us to read.

re: "I get a label not defined on the On Error GoTo Changedate_Err: "

A GoTo statement is not used to call another sub-routine, it is used to jump to a specific location in the same routine. Your GoTo is looking for that label in the routine where the GoTo was executed and not finding it.

I've offered 2 different versions of the code. The first piece of code uses an On Error GoTo method of handling the issue of the sheet already existing. It creates the new sheet (Fresh(2)) and then handles the error that is created when the code tries to name the sheet with the name of an existing sheet.

In my opinion, this is an inefficient way to deal with the issue, but I've included it so that you can see how it would be done. It's mainly there to show you how a typical error handing routine is used.

The second piece of code simply checks to see if a sheet with the desired name exists before creating it. If it exists, it informs the user and exits. If not, it creates it. No sense in creating and deleting a sheet if it's not needed.

I also combined a few lines to make the code even more efficient. Rarely do you have to select an object via VBA in order to perform an operation on it. You can usually work directly with the object.

For example, these lines:

Sheets("Fresh").Select
 Sheets("Fresh").Copy

can be reduced to:

Sheets("Fresh").Copy

Let me know if you have any other questions.

Sub NewDateDheet_1()
'Handle error if sheet exists
On Error GoTo shtExists
'Add Sheet As Last Sheet
  Sheets("Fresh").Copy After:=Sheets(Sheets.Count)
    With ActiveSheet
     .Name = Month(Date) & "-" & Day(Date) & "-" & _
             Right(Year(Date), 2)
'Set the current Date
     .Range("A2") = Now
    End With
  Exit Sub
shtExists:
'Delete last sheet without warning
   Application.DisplayAlerts = False
     Sheets(Sheets.Count).Delete
   Application.DisplayAlerts = True
'Inform user
    MsgBox "The sheet for this date already exists"
End Sub


Sub NewDateDheet_2()
'Inform User and Exit Sub if sheet exists
  If Sheets(Sheets.Count).Name = _
    Month(Date) & "-" & Day(Date) & "-" & _
    Right(Year(Date), 2) Then
      MsgBox "The sheet for this date already exists"
      Exit Sub
  End If
'Add Sheet as Last Sheet if it doesn't exist
  Sheets("Fresh").Copy After:=Sheets(Sheets.Count)
   With ActiveSheet
    .Name = Month(Date) & "-" & Day(Date) & "-" & Right(Year(Date), 2)
'Set the current Date
    .Range("A2") = Now
   End With
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#3
November 23, 2010 at 09:01:31
Thanks for the help works great. Sorry about the code I will do a better job next time I have a problem. I do have a question about a peice of your code tho. The Application.DisplayAlerts = False code will that disable all alerts to the end user?

Report •

Related Solutions

#4
November 23, 2010 at 09:38:29
Application.DisplayAlerts = False will indeed disable all alerts to the end user, but notice how it is used:

  Application.DisplayAlerts = False
     Sheets(Sheets.Count).Delete
   Application.DisplayAlerts = True

This section disables Alerts so that the user does not see the "Data may exist in the sheet..." message when the Fresh(2) sheet is deleted, but the next line turns the Alerts right back on.

As long as the code runs through completion, the user will not be impacted.

However, I want to make sure that you noticed that I recommended that you don't use the routine that contains those lines. There is no point in causing an error and then handling the error when there are more efficient ways to accomplish your goal - specifically, this code:

Sub NewDateDheet_2()
'Inform User and Exit Sub if sheet exists
  If Sheets(Sheets.Count).Name = _
    Month(Date) & "-" & Day(Date) & "-" & _
    Right(Year(Date), 2) Then
      MsgBox "The sheet for this date already exists"
      Exit Sub
  End If
'Add Sheet as Last Sheet if it doesn't exist
  Sheets("Fresh").Copy After:=Sheets(Sheets.Count)
   With ActiveSheet
    .Name = Month(Date) & "-" & Day(Date) & "-" & Right(Year(Date), 2)
'Set the current Date
    .Range("A2") = Now
   End With
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Ask Question