Code to Delete Sheet Tabs

[Justinian]

I have a code I run to split data into tab named after managers. All managers are together on sheet1 and after I run the code, each manager's data is on its own tab. The problem is the when the code is run again, it adds data on each tab instead of replacing the data on each tab. How can I construct some code so Excel deletes all tabs to the right of my data sheet before running my original code to separate tabs?

 

[My Aswer Is This]

Thanks Michael:
I did not remember this line of his post:
Excel deletes all tabs to the right of my data sheet.

 

[Justinian]

I just want to clear the sheets the macro creates or only clear sheets to the right of the All Accounts tab. Is that doable?

 

[Justinian]

Install the script I made and call it this way: Note text in "Red"

Sub SplitDivisions()

 Dim ws As Worksheet
 Dim wsOut As Worksheet
 Dim i As Long
 Dim lr As Long
 Dim TabName As String
 Dim Found As Boolean
[COLOR="#FF0000"]Call Clear_All_Sheets[/COLOR]
 With ThisWorkbook.Worksheets("All Accounts")
 For i = 2 To .Cells(.Rows.Count, "H").End(xlUp).Row
 TabName = CStr(.Cells(i, "H"))
 Found = False
 For Each ws In Worksheets
 If ws.Name = TabName Then Found = True
 Next
 If Found Then
 Set wsOut = Worksheets(TabName)
 Else
 Set wsOut = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
 wsOut.Name = TabName
 .Range("A1:Q1").Copy
 With wsOut.Range("A1:Q1")
 .PasteSpecial xlPasteAll
 .PasteSpecial xlPasteColumnWidths
 End With
 End If
 lr = wsOut.Cells(wsOut.Rows.Count, "H").End(xlUp).Row
 .Range("A1:Q1").Offset(i - 1).Copy
 wsOut.Range("A1:Q1").Offset(lr).PasteSpecial xlPasteAll
 Next
 End With

 End Sub

This code is not clearing the existing data.

 

[Michael M]

did you try my code ??.....and change the sheet name "Master" to "All Accounts"

Sub SplitDivisions()

 Dim ws As Worksheet
 Dim wsOut As Worksheet
 Dim i As Long
 Dim lr As Long
 Dim TabName As String
 Dim Found As Boolean
 With ThisWorkbook.Worksheets("All Accounts")
 For i = 2 To .Cells(.Rows.Count, "H").End(xlUp).Row
 TabName = CStr(.Cells(i, "H"))
 Found = False
 For Each ws In Worksheets
 If ws.Name = TabName Then Found = True
 Next
 If Found Then
 Set wsOut = Worksheets(TabName)
 Else
 Set wsOut = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
 wsOut.Name = TabName
 .Range("A1:Q1").Copy
 With wsOut.Range("A1:Q1")
 .PasteSpecial xlPasteAll
 .PasteSpecial xlPasteColumnWidths
 End With
 End If
 lr = wsOut.Cells(wsOut.Rows.Count, "H").End(xlUp).Row
 .Range("A1:Q1").Offset(i - 1).Copy
 wsOut.Range("A1:Q1").Offset(lr).PasteSpecial xlPasteAll
 Next
 End With
Call Clear_All_Sheets
 End Sub

 

[Justinian]

did you try my code ??.....and change the sheet name "Master" to "All Accounts"

Sub SplitDivisions()

 Dim ws As Worksheet
 Dim wsOut As Worksheet
 Dim i As Long
 Dim lr As Long
 Dim TabName As String
 Dim Found As Boolean
 With ThisWorkbook.Worksheets("All Accounts")
 For i = 2 To .Cells(.Rows.Count, "H").End(xlUp).Row
 TabName = CStr(.Cells(i, "H"))
 Found = False
 For Each ws In Worksheets
 If ws.Name = TabName Then Found = True
 Next
 If Found Then
 Set wsOut = Worksheets(TabName)
 Else
 Set wsOut = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
 wsOut.Name = TabName
 .Range("A1:Q1").Copy
 With wsOut.Range("A1:Q1")
 .PasteSpecial xlPasteAll
 .PasteSpecial xlPasteColumnWidths
 End With
 End If
 lr = wsOut.Cells(wsOut.Rows.Count, "H").End(xlUp).Row
 .Range("A1:Q1").Offset(i - 1).Copy
 wsOut.Range("A1:Q1").Offset(lr).PasteSpecial xlPasteAll
 Next
 End With
Call Clear_All_Sheets
 End Sub

Yep. It is adding to the existing code and not clearing old data.

 

[My Aswer Is This]

The script I wrote should clear all the sheets in your workbook. Now if you do not want a particular sheet to be cleared then notice Michaels modified script of mine. You must have the script installed in the workbook.
I always test my scripts.

 

[Justinian]

Ok but where in my code does your script go?

 

[My Aswer Is This]

I believe the:
Call Clear_All_Sheets
Needs to be at the beginning of the script not at the end.

 

[My Aswer Is This]

The script I put in post # 9 is what you should use.
You only need that one line of code I entered in Red
When the script gets to that line it runs the script called Clear_All_Sheets
Now my script clears all sheets if you want your Master sheet not cleared then I need to know the name of the sheet you do not want cleared. Is the sheet named "Master"?

 

[Justinian]

I believe the:
Call Clear_All_Sheets
Needs to be at the beginning of the script not at the end.

Forgive me but I am not sure what to do with the Call Clear_All_Sheets. When I run the code, I get a debug error on that line.

I am a beginner to VBA.