Add sheets, in specific order, if missing

Question

I'm setting up a workbook that has two sheets. One sheet is for a data set and the second sheet is for analysis.
The data set sheet will be first (on the left/Sheet1) followed by the analysis sheet second (on the right/Sheet2).
Each sheet Name will have today's date and a title.

I would like to check if both sheets are present for today's date.

If Sheet1 is missing, add on the left.
If Sheet2 is missing, add on the right.
If both are missing, add both.
There should be no other sheets.

I have two modules. One checks for one sheet, and one checks for the other.

Option Explicit

Public szTodayRtsMU As String
Dim szTodayRawData As String

' Add and name a sheet with today's date.
Sub AddRtsMUsSheets_Today()
     
     ' Date and title.
    szTodayRtsMU = Format(Date, "dd-mm-yyyy") & " " & "Rts & MUs"
     
    On Error GoTo MakeSheet
     
     ' Check if sheet already exists, if it does, select activate it.
    Sheets(szTodayRtsMU).Activate
     
     ' No errors, code is done.
    Exit Sub
     
    MakeSheet:
     ' If the sheet doesn't exist, add it.
    ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
     ' Name it
    ActiveSheet.Name = szTodayRtsMU
End Sub

Sub AddRawDataSheets_Today()
     
     ' Date and title.
    szTodayRawData = Format(Date, "dd-mm-yyyy") & " " & "Raw Data"
     
    On Error GoTo MakeSheet
     
     ' Check if sheet already exists, if it does, select activate it.
    Sheets(szTodayRawData).Activate
     
     ' No errors, code is done.
    Exit Sub
        
    MakeSheet:
     ' If the sheet doesn't exist, add it.
    ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
     ' Name it
    ActiveSheet.Name = szTodayRawData
End Sub

Which is the pattern for the title on the sheets? how is called the data sheet and the analisys sheet? `Raw Data` and `"Rts & MUs"`? — Damian, Jun 05 '19 at 11:00
The titles I'm using at the moment are for example, '05-06-2019 Raw Data' (data set) and '05-06-2019 Rts & MU' (analysis sheet). — bennyjim, Jun 05 '19 at 11:05
Are `Raw Data` and `Rts & MU`always on the sheet name? Only the dates change, right? — Damian, Jun 05 '19 at 11:07
Last question, the code is on the same workbook you want to check, or in another workbook? — Damian, Jun 05 '19 at 11:22
The code is in the same workbook. Apologies, I should have specified initially! — bennyjim, Jun 05 '19 at 11:23

score 0 · Accepted Answer · answered Jun 05 '19 at 11:33

0

Tested, 100% working:

Option Explicit
Sub CheckForWorksheets()

    Dim szTodayRawData As String
    Dim szTodayRtsMU As String
    Dim ws As Worksheet
    Dim countRawData As Byte 'check if exists the RawData sheet
    Dim countRTsMU As Byte 'check if exists the RtsMU sheet

    'Date and titles
    szTodayRawData = Format(Date, "dd-mm-yyyy") & " " & "Raw Data"
    szTodayRtsMU = Format(Date, "dd-mm-yyyy") & " " & "Rts & MUs"

    'Initialize the counters with 1
    countRawData = 1
    countRTsMU = 1

    'This is a loop on all the worksheets on this workbook
    For Each ws In ThisWorkbook.Worksheets
        'If the sheets exists then the counter goes to 0
        If ws.Name = szTodayRawData Then
            countRawData = 0
        ElseIf ws.Name = szTodayRtsMU Then
            countRTsMU = 0
        End If
    Next ws

    'Add the sheets if needed
    With ThisWorkbook
        If countRawData = 1 Then
            Set ws = .Sheets.Add(before:=.Sheets(.Sheets.Count))
            ws.Name = szTodayRawData
        End If
        If countRTsMU = 1 Then
            Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
            ws.Name = szTodayRtsMU
        End If
    End With

    'Delete any other sheet
    For Each ws In ThisWorkbook.Sheets
        If Not ws.Name = szTodayRawData And Not ws.Name = szTodayRtsMU Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next ws

End Sub

If you need help understanding the code ask me anything.

answered Jun 05 '19 at 11:33

Damian

5,152
1
10
21

1

Why do you use a numeric instead of a boolean to keep track if a sheet is there? – FunThomas Jun 05 '19 at 11:46
@FunThomas could actually have done that... But I didn't think about booleans... Doesn't matter here I'm using `Byte` so it's the same size on memory, right? – Damian Jun 05 '19 at 11:48
1

**(a)** Byte, Integer, Long and Boolean all uses 4 Bytes, see https://stackoverflow.com/a/26409520/7599798. **(b)** Always use the appropriate data type. If you use a boolean, it's obvious that it can be only `True` or `False`. A numeric variable can get any number, and when you look at the code, you need to figure out what a number means. In your case, 1 means *not found*, 0 means *found*, but you need to look carefully to the code to see that. – FunThomas Jun 05 '19 at 11:57
@FunThomas - thank you very much for your input. I'm trying to make sure I ingrain good practice from the offset. Would the rest of the code need to change much if a Boolean type was used? – bennyjim Jun 05 '19 at 12:26
No. Use e.g. `rawDataFound as boolean` instead of countRawData, write `rawDataFound = True` when the sheet was found and `If Not rawDataFound Then` when checking before the ` .Sheets.Add`-statement. – FunThomas Jun 05 '19 at 12:37
Great, thank you. That seems to work! @FunThomas or @Damian - sorry one last question - regarding `Set ws = .Sheets.Add(before:=.Sheets(.Sheets.Count))` why is there a period at the start of `.Sheets`? That's the only bit of the code I don't understand... – bennyjim Jun 05 '19 at 13:15
@bennyjim To avoid writting full path to the range, you can use `With` And your workbook/worksheet/range object so you don't need to write it everytime. So instead of `Set ws = ThisWorkbook.Sheets.Add(before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))`. You just use a `With ThisWorkbook` and anything referencing the workbook will lead with the period. – Damian Jun 05 '19 at 13:31
Ahh I see! Much appreciated Damian. – bennyjim Jun 05 '19 at 13:37

Add sheets, in specific order, if missing

1 Answers1