Add Worksheets if Does Not Exist

[billandrew]

Have been searching How to add worksheets if does not exist and then add totals to Column B. Not sure How to execute the top portion of the code.

thank you

Sub TotalEachWorksheet()    Grandtotal = 0
    Worksheets.Add
    ActiveSheet.Name = "Summary"
    Worksheets.Add
    ActiveSheet.Name = "Code"
    
    With ThisWorkbook
    For i = 1 To .Sheets.Count
        If .Sheets(i).Name = "Summary" Then
        blnfound = True
        Exit For
        End If
        Next i
        End With
        
    
  
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Summary" Or ws.Name <> "Code" Then
        Total = ws.Cells(Rows.Count, 2).End(xlUp).Value
        ThisSheet = ws.Name
        Grandtotal = Grandtotal + Total
        End If
        Next ws
        
        
        With Sheets("Summary")
        .Cells(1, 2).Value = Grandtotal
        .Cells(1, 1).Value = "GrandTotal"
        .Cells(1, 1).Font.Bold = True
        .Cells(1, 1).Select
        End With
        End Sub

 

[jolivanes]

Just describe what you want to achieve. For me, you don't need to add code that does not work.

 

[billandrew]

Looking to add sheets named Summary and Code. If the sheet already exists in the workbook, then there is no need to add. Then Summary sheet will contain the sum of Column B in the other sheets in workbook.

 

[JonXL]

Looking to add sheets named Summary and Code. If the sheet already exists in the workbook, then there is no need to add. Then Summary sheet will contain the sum of Column B in the other sheets in workbook.

Add this:

Sub NewSheet(Wb As Workbook, SheetName As String)
Dim Alerts As Boolean
Dim ws As Worksheet

Alerts = Application.DisplayAlerts

Application.DisplayAlerts = False

On Error Resume Next
Err.Clear
Set ws = Wb.Worksheets.Add
ws.Name = SheetName
If Err.Number > 0 Then ws.Delete
Err.Clear

Application.DisplayAlerts = Alerts


End Sub

Call it from your other routines using:

NewSheet ThisWorkbook, "Summary"
NewSheet ThisWorkbook, "Code"

 

[billandrew]

This did it. Thanks

How does the NewSheet recognize the sheets names Summary & Code

 

[JonXL]

Are you asking how the code works?

It creates a new sheet and then tries to name it xyz. If xyz already exists, you get an error. The On Error Resume Next keeps the procedure from tossing up an error message. Instead, we look for the error after trying to name the worksheet. If it already exists, we get an error. In that case we need to delete the new worksheet so we don't have an extra sheet hanging around.

Then we clear the error.

Then we reset the error handler (oops, I forgot that so you should add it.)

On Error Goto 0

The end...

 

[Peter_SSs]

Here is another way that only adds a new sheet if required, rather than adding and then removing if not required.

Sub Insert_Sheets()
  Dim wsSummary As Worksheet, wsCode As Worksheet
  
  On Error Resume Next
    Set wsSummary = Sheets("Summary")
    Set wsCode = Sheets("Code")
  On Error GoTo 0
  If wsSummary Is Nothing Then
    Sheets.Add.Name = "Summary"
    Set wsSummary = Sheets("Summary")
  End If
  If wsCode Is Nothing Then
    Sheets.Add.Name = "Code"
    Set wsCode = Sheets("Code")
  End If
  With wsSummary
    'Code here to do what you want with the Summary sheet
    
  End With
  With wsCode
    'Code here to do what you want with the Code sheet
    
  End With
End Sub

BTW, your code line ..

If ws.Name <> "Summary" Or ws.Name <> "Code" Then

.. will be True for every worksheet. So if you want to do something with all sheets except those two, that Or need to be changed to And

 

[billandrew]

Thank You Mr. Peter.

You said stay on this Forum and I'll learn. I'm Staying!!!

 

[billandrew]

My apologies. Thank you JonXL for your help and explanation.

Always appreciated.