CODEWITHSUNDEEP
excelvba
bennyjim
bennyjim

Reputation: 7

Add sheets, in specific order, if missing

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.

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

Upvotes: 0

Views: 151

Answers (1)

Damian
Damian

Reputation: 5174

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.

Upvotes: 0

Related Questions