New Sheet Macro Error Handling

[cameronbenson]

Hi All,

I'm looking for some error handling options for a workbook I'm providing support for. It records orders on a daily basis by creating a new sheet for each day. I have a macro that I need in every new sheet that's created. I created a master sheet with a workbook code that will copy the Master sheet and rename it with today's date. However, occasionally my customers need to create a new sheet to perform other tasks and i don't want the master sheet to copy over if they create a new sheet on the same day. For example, if today is 2/8 and there isnt a sheet name with 2/8 yet (ie, they are creating the record sheet at the beginning of the day), I want it to copy the master sheet and rename it. But if there is already a record sheet (ie they are creating a new sheet later in the day) i want it to create a new sheet and display with just a temporary name like what excel normally defaults to like Sheet4.

Thank you all in advance for your assistance.

This is my workbook code.

Private Sub Workbook_NewSheet(ByVal Sh As Object)    Dim tmpName As String


    tmpName = Sh.Name
    Sheets("MyMaster").Copy Before:=Sheets(Sh.Name)
    Application.DisplayAlerts = False
    Sheets(Sh.Name).Delete
    Application.DisplayAlerts = True
    Sheets("MyMaster (2)").Name = Format(Date, "m.d")
End Sub

if it makes a difference, the code that's on my master sheet is this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range
    
    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("A:A"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, 2)
                    .Value = Now
                    .NumberFormat = "hh:mm:ss"
                End With
            Else
                rCell.Offset(0, 2).Clear
            End If
        Next
    End If


ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

 

[cameronbenson]

Hi All,

I'm looking for some error handling options for a workbook I'm providing support for. It records orders on a daily basis by creating a new sheet for each day. I have a macro that I need in every new sheet that's created. I created a master sheet with a workbook code that will copy the Master sheet and rename it with today's date. However, occasionally my customers need to create a new sheet to perform other tasks and i don't want the master sheet to copy over if they create a new sheet on the same day. For example, if today is 2/8 and there isnt a sheet name with 2/8 yet (ie, they are creating the record sheet at the beginning of the day), I want it to copy the master sheet and rename it. But if there is already a record sheet (ie they are creating a new sheet later in the day) i want it to create a new sheet and display with just a temporary name like what excel normally defaults to like Sheet4.

Thank you all in advance for your assistance.

This is my workbook code.

Private Sub Workbook_NewSheet(ByVal Sh As Object)    Dim tmpName As String


    tmpName = Sh.Name
    Sheets("MyMaster").Copy Before:=Sheets(Sh.Name)
    Application.DisplayAlerts = False
    Sheets(Sh.Name).Delete
    Application.DisplayAlerts = True
    Sheets("MyMaster (2)").Name = Format(Date, "m.d")
End Sub

if it makes a difference, the code that's on my master sheet is this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range
    
    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("A:A"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, 2)
                    .Value = Now
                    .NumberFormat = "hh:mm:ss"
                End With
            Else
                rCell.Offset(0, 2).Clear
            End If
        Next
    End If


ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Nevermind, i got it. It took some digging, but i was able to back my way into the code needed.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    On Error GoTo Handling
    Dim tmpName As String
    tmpName = Sh.Name
    Sheets("MyMaster").Copy Before:=Sheets(Sh.Name)
    Application.DisplayAlerts = False
    Sheets(Sh.Name).Delete
    Application.DisplayAlerts = True
    Sheets("MyMaster (2)").Name = Format(Date, "m/d/yy")
    Exit Sub
Handling:
    Sheets("MyMaster (2)").Name = tmpName
End Sub