Test if sheet exists

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
261
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()

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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try this
If the sheet exists the sub will Exit after the MsgBox
VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
You could also shorten it a little...
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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
Top