Add Worksheets if Does Not Exist

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
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

Code:
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Just describe what you want to achieve. For me, you don't need to add code that does not work.
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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:

Code:
NewSheet ThisWorkbook, "Summary"
NewSheet ThisWorkbook, "Code"
 
Last edited:
Upvote 0
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...
 
Upvote 0
Here is another way that only adds a new sheet if required, rather than adding and then removing if not required.

Rich (BB code):
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 ..
Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top