Code to Delete Sheet Tabs

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,557
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You don't have to necessarily delete the tabs. You could just delete the existing data on each tab before copying the new data to it. Something like
Code:
 UsedRange.ClearContents
 
Last edited:
Upvote 0
That gives me a debug error. Where in the following code would I place your suggestion?

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

End Sub
 
Upvote 0
Hi Justinian. I think that I have good idea of what you want to do but it would be easier to suggest and test a solution if I could see your actual file. If the file has confidential data, you could replace it with generic data. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells,ranges and worksheets.
 
Last edited:
Upvote 0
Here you go. Thank you for your time working on this:

https://app.box.com/s/50om2ea309vki7nsqs3zi4z72g85husj

When you run the macro, every unique name gets placed on its own tab (with the data associated with said name). If you make any changes to the data set (All Accounts tab), the macro adds data to the existing data from the first time you ran the macro. I am trying to get the code to delete ALL data before the macro executes. So on the tabs with people's names, I want any extant data to be deleted prior to the macro separating the names and placing them on their own tabs.
 
Upvote 0
It will probably be a lot easier to use a Pivot Table. If you create a master pivot table that mirrors your current output, you can put Manager name in the pivot table's Filter Field. Then in pivot table Options, select "Show Report Filter Pages", and Excel will create a new pivot table worksheet for each name in the filter.

Then as you add new data all you do is refresh the pivot tables and you're done.
 
Upvote 0
To clear all sheets of their content use this script:
Code:
Sub Clear_All_Sheets()
Dim i As Integer
    For i = 1 To Sheets.Count
    Sheets(i).Cells.ClearContents
    Next
End Sub
 
Upvote 0
To clear all sheets of their content use this script:
Code:
Sub Clear_All_Sheets()
Dim i As Integer
    For i = 1 To Sheets.Count
    Sheets(i).Cells.ClearContents
    Next
End Sub

Where in this code would that go?

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

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

Code:
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
 
Upvote 0
the code provided in Post #7 will clear ALL sheets.
I'm guessing you want all sheets EXCEPT the Master sheet
So the same code modified
Code:
Sub Clear_All_Sheets()
Dim i As Integer
    For i = 1 To Sheets.Count
      If Sheets(i).Name <> "Master" Then 
        Sheets(i).Cells.ClearContents
      End if
Next i
End Sub
 
Upvote 0

Forum statistics

Threads
1,220,517
Messages
6,154,393
Members
451,208
Latest member
campbell1093

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