VBA to avoid duplicate sheet

S

Shadkng

Active Member
Joined
Oct 11, 2018
Messages
365
Hi, the below code creates a new sheet "PARTS_LIST_CLUTCH." Sometimes I have to run the code again and I get a debug because the sheet name already exists. Is it tpossible to delete the sheet and re-create it? Or if we could just overwrite it that would be fine too. Thanks for the help.

VBA Code: 
Sub PARTS_LIST_CLUTCH()  'steve added 10-26-21
Application.ScreenUpdating = False

If Sheets("FINALORDER").Range("B23").Value = "SR" Then '10/17/2019 - Added "SR" Condition
    Exit Sub
    Else
End If

Sheets("PARTS LIST").Copy after:=Sheets("PARTS LIST")
ActiveSheet.Name = "PARTS LIST CLUTCH"
Range("A7:F300").Clear
Range("E1:F5").Clear
ActiveSheet.Buttons.Delete

Worksheets("PARTS LIST").Range("E7:F50").Copy
Worksheets("PARTS LIST CLUTCH").Range("A11").PasteSpecial


Range("A1") = "** PARTS LIST CLUTCH ASSEMBLY **"
Range("A8") = "PACKED BY:________________"
Range("A6").HorizontalAlignment = xlLeft
Range("A6") = "DATE:________________"
Range("C8") = "CHECKED BY:________________"
Range("a1").Font.Size = 24
Columns("A:A").AutoFit
Columns("C:C").AutoFit
Columns("A:A").ColumnWidth = Columns("A:A").ColumnWidth + 1
Columns("B:B").HorizontalAlignment = xlCenter
Range("A10").Select

Application.ScreenUpdating = True

End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
How about
VBA Code: 
Sub PARTS_LIST_CLUTCH()  'steve added 10-26-21
Application.ScreenUpdating = False
'
If Sheets("FINALORDER").Range("B23").Value = "SR" Then '10/17/2019 - Added "SR" Condition
    Exit Sub
    Else
End If
If Not Evaluate("isref('PARTS LIST CLUTCH'!a1)") Then
   Sheets("PARTS LIST").Copy after:=Sheets("PARTS LIST")
   ActiveSheet.Name = "PARTS LIST CLUTCH"
End If
Range("A7:F300").Clear
Range("E1:F5").Clear
ActiveSheet.Buttons.Delete

Worksheets("PARTS LIST").Range("E7:F50").Copy
Worksheets("PARTS LIST CLUTCH").Range("A11").PasteSpecial


Range("A1") = "** PARTS LIST CLUTCH ASSEMBLY **"
Range("A8") = "PACKED BY:________________"
Range("A6").HorizontalAlignment = xlLeft
Range("A6") = "DATE:________________"
Range("C8") = "CHECKED BY:________________"
Range("a1").Font.Size = 24
Columns("A:A").AutoFit
Columns("C:C").AutoFit
Columns("A:A").ColumnWidth = Columns("A:A").ColumnWidth + 1
Columns("B:B").HorizontalAlignment = xlCenter
Range("A10").Select

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Fluff said:
How about
VBA Code: 
Sub PARTS_LIST_CLUTCH()  'steve added 10-26-21
Application.ScreenUpdating = False
'
If Sheets("FINALORDER").Range("B23").Value = "SR" Then '10/17/2019 - Added "SR" Condition
    Exit Sub
    Else
End If
If Not Evaluate("isref('PARTS LIST CLUTCH'!a1)") Then
   Sheets("PARTS LIST").Copy after:=Sheets("PARTS LIST")
   ActiveSheet.Name = "PARTS LIST CLUTCH"
End If
Range("A7:F300").Clear
Range("E1:F5").Clear
ActiveSheet.Buttons.Delete

Worksheets("PARTS LIST").Range("E7:F50").Copy
Worksheets("PARTS LIST CLUTCH").Range("A11").PasteSpecial


Range("A1") = "** PARTS LIST CLUTCH ASSEMBLY **"
Range("A8") = "PACKED BY:________________"
Range("A6").HorizontalAlignment = xlLeft
Range("A6") = "DATE:________________"
Range("C8") = "CHECKED BY:________________"
Range("a1").Font.Size = 24
Columns("A:A").AutoFit
Columns("C:C").AutoFit
Columns("A:A").ColumnWidth = Columns("A:A").ColumnWidth + 1
Columns("B:B").HorizontalAlignment = xlCenter
Range("A10").Select

Application.ScreenUpdating = True

End Sub
Click to expand...
It runs OK the first time, before "PARTS LIST CLUTCH" is created, but running it the second time blanks out both sheets "PARTS LIST" AND "PARTS LIST CLUTCH". Would it make sense on the second run to delete "PARTS LIST CLUTCH" and just let it create it again?
 
Upvote 0
Maybe
VBA Code: 
Sub PARTS_LIST_CLUTCH()  'steve added 10-26-21
Application.ScreenUpdating = False
 Dim Sht As Worksheet
If Sheets("FINALORDER").Range("B23").Value = "SR" Then '10/17/2019 - Added "SR" Condition
    Exit Sub
    Else
End If
If Not Evaluate("isref('PARTS LIST CLUTCH'!a1)") Then
   Sheets("PARTS LIST").Copy after:=Sheets("PARTS LIST")
   ActiveSheet.Name = "PARTS LIST CLUTCH"
Else
   Sheets("PARTS LIST CLUTCH").UsedRange.Clear
End If
With Sheets("PARTS LIST CLUTCH")
   .Range("A7:F300").Clear
   .Range("E1:F5").Clear
   .Buttons.Delete

   Worksheets("PARTS LIST").Range("E7:F50").Copy
   .Range("A11").PasteSpecial


   .Range("A1") = "** PARTS LIST CLUTCH ASSEMBLY **"
   .Range("A8") = "PACKED BY:________________"
   .Range("A6").HorizontalAlignment = xlLeft
   .Range("A6") = "DATE:________________"
   .Range("C8") = "CHECKED BY:________________"
   .Range("a1").Font.Size = 24
   .Columns("A:A").AutoFit
   .Columns("C:C").AutoFit
   .Columns("A:A").ColumnWidth = Columns("A:A").ColumnWidth + 1
   .Columns("B:B").HorizontalAlignment = xlCenter
End With

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Fluff said:
Maybe
VBA Code: 
Sub PARTS_LIST_CLUTCH()  'steve added 10-26-21
Application.ScreenUpdating = False
 Dim Sht As Worksheet
If Sheets("FINALORDER").Range("B23").Value = "SR" Then '10/17/2019 - Added "SR" Condition
    Exit Sub
    Else
End If
If Not Evaluate("isref('PARTS LIST CLUTCH'!a1)") Then
   Sheets("PARTS LIST").Copy after:=Sheets("PARTS LIST")
   ActiveSheet.Name = "PARTS LIST CLUTCH"
Else
   Sheets("PARTS LIST CLUTCH").UsedRange.Clear
End If
With Sheets("PARTS LIST CLUTCH")
   .Range("A7:F300").Clear
   .Range("E1:F5").Clear
   .Buttons.Delete

   Worksheets("PARTS LIST").Range("E7:F50").Copy
   .Range("A11").PasteSpecial


   .Range("A1") = "** PARTS LIST CLUTCH ASSEMBLY **"
   .Range("A8") = "PACKED BY:________________"
   .Range("A6").HorizontalAlignment = xlLeft
   .Range("A6") = "DATE:________________"
   .Range("C8") = "CHECKED BY:________________"
   .Range("a1").Font.Size = 24
   .Columns("A:A").AutoFit
   .Columns("C:C").AutoFit
   .Columns("A:A").ColumnWidth = Columns("A:A").ColumnWidth + 1
   .Columns("B:B").HorizontalAlignment = xlCenter
End With

Application.ScreenUpdating = True

End Sub
Click to expand...
 
Upvote 0
This works better. However, on the 2nd run I lose the formatting on "PARTS LIST CLUTCH".
 
Upvote 0
In that case change .Clear to .ClearContents
 
Upvote 0
Please stop making posts that contain nothing but a quote.
 
Upvote 0
Actually, it running the below lines again which is erasing a bunch of other things. Let me check further and I'll come back to you with more detail.

With Sheets("PARTS LIST CLUTCH")
.Range("A7:F300").Clear
.Range("E1:F5").Clear
.Buttons.Delete
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
101
ipbr21054
I
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
429
Legacy 352679
L
R
  • Question
Macro not working after adding two new items
Replies
4
Views
136
myall_blues
myall_blues
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
395
sspatriots
S
J
  • Question
Consolidate data from multiple sheets based on values selected in dependent list boxes
Replies
0
Views
229
jconda
J

Forum statistics

Threads
1,215,409
Messages
6,124,730
Members
449,185
Latest member
ekrause77

Share this page

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