Test if sheet exists

[Shadkng]

Hi, I was wondering if someone can add code to below to test if the worksheet "TOT FAB" exists - if it does not exist then let the existing code create it. If it does exist, then I would like to overwrite the data. Thanks in advance.

Sub TOT_FAB()

Application.ScreenUpdating = False
Range("A8:C" & Range("A8").End(xlDown).Row).Copy
Sheets.Add(After:=Sheets("PARTS LIST")).name = "TOT FAB"

Worksheets("TOT FAB").Range("A3").PasteSpecial Paste:=xlPasteFormulas

Range("C:C").Select
    Selection.TextToColumns Destination:=Range("C3"), DataType:=xlDelimited, _
        OtherChar:="X", FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(9, 1)), _
        TrailingMinusNumbers:=True
    Range("E3").Select
    
Columns("A:A").AutoFit
Columns("B:B").HorizontalAlignment = xlCenter
Columns("C:E").HorizontalAlignment = xlLeft
Columns("B:B").ColumnWidth = 5
Columns("C:D").ColumnWidth = 6

Range("A1") = "DESCRIPTION"
Range("B1") = "QUA"
Range("C1") = "WID"
Range("D1") = "LEN"

Application.ScreenUpdating = True

End Sub

 

[Michael M]

Try this
If the sheet exists the sub will Exit after the MsgBox

Sub MM1()

Application.ScreenUpdating = False
Dim wsSheet As Worksheet
On Error Resume Next
Set wsSheet = Sheets("TOT FAB")
On Error GoTo 0
If Not wsSheet Is Nothing Then
MsgBox "Ws does exist"
Exit Sub
Else
MsgBox "Ws does NOT exist"
End If
Range("A8:C" & Range("A8").End(xlDown).Row).Copy
Sheets.Add(After:=Sheets("PARTS LIST")).Name = "TOT FAB"
Worksheets("TOT FAB").Range("A3").PasteSpecial Paste:=xlPasteFormulas

Range("C:C").Select
    Selection.TextToColumns Destination:=Range("C3"), DataType:=xlDelimited, _
        OtherChar:="X", FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(9, 1)), _
        TrailingMinusNumbers:=True
    Range("E3").Select
    
Columns("A:A").AutoFit
Columns("B:B").HorizontalAlignment = xlCenter
Columns("C:E").HorizontalAlignment = xlLeft
Columns("B:B").ColumnWidth = 5
Columns("C:D").ColumnWidth = 6

Range("A1") = "DESCRIPTION"
Range("B1") = "QUA"
Range("C1") = "WID"
Range("D1") = "LEN"

Application.ScreenUpdating = True

End Sub

 

[Michael M]

You could also shorten it a little...

Sub MM1()
Application.ScreenUpdating = False
Dim wsSheet As Worksheet
Set wsSheet = Sheets("TOT FAB")
If Not wsSheet Is Nothing Then
MsgBox "Ws does exist"
Exit Sub
Else
MsgBox "Ws does NOT exist"
End If
Range("A8:C" & Range("A8").End(xlDown).Row).Copy
Sheets.Add(After:=Sheets("PARTS LIST")).Name = "TOT FAB"
Worksheets("TOT FAB").Range("A3").PasteSpecial Paste:=xlPasteFormulas
Range("C:C").TextToColumns Destination:=Range("C3"), DataType:=xlDelimited, _
        OtherChar:="X", FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(9, 1)), _
        TrailingMinusNumbers:=True
Columns("A:A").AutoFit
Columns("B:B").HorizontalAlignment = xlCenter
Columns("C:E").HorizontalAlignment = xlLeft
Columns("B:B").ColumnWidth = 5
Columns("C:D").ColumnWidth = 6
[A1:D1] = Array("DESCRIPTION", "QUA", "WID", "LEN")
Application.ScreenUpdating = True
End Sub