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?

 

[Justinian]

My data sheet is named All Accounts.

 

[Justinian]

I have other sheets to the left of the All Accounts tab that I also do not want cleared.

 

[MARK858]

Try running the code below as a standalone. It should clear all the sheets to the right of the "All Accounts" sheet.

Sub clearshts()
    Dim i As Long
    For i = Sheets("All Accounts").Index + 1 To Sheets.Count
        Sheets(i).Cells.ClearContents
    Next
End Sub

 

[My Aswer Is This]

I see now. Mark has the answer. I was still pondering that one.

 

[Justinian]

Try running the code below as a standalone. It should clear all the sheets to the right of the "All Accounts" sheet.

Sub clearshts()
    Dim i As Long
    For i = Sheets("All Accounts").Index + 1 To Sheets.Count
        Sheets(i).Cells.ClearContents
    Next
End Sub

Is there no way to make this part of the first code?

 

[Justinian]

This works! The only problem is that it clears the data but leaves formatting like borders and shading. How can I fix that?

 

[MARK858]

This works! The only problem is that it clears the data but leaves formatting like borders and shading. How can I fix that?

You would change

Sheets(i).Cells.ClearContents

to

Sheets(i).Cells.Delete

Is there no way to make this part of the first code?

Of course it can be part of another code, the reason for testing it separately is to make sure it works without anything else interfering with it before integrating it in the code.

Put

Dim irw As Long

with the rest of the lines starting with Dim.

then put the lines below at the point that you want the sheets cleared (you can use F8 to step through your code line by line if you need to see what your code does at each line).

    For irw = Sheets("All Accounts").Index + 1 To Sheets.Count
        Sheets(irw).Cells.Delete
    Next

Please note that I only changed the variable from i to irw as you are already using, i just to make it less confusing.

 

[mumps]

Make sure that the "All Accounts" sheet is the active sheet and try this macro:

Sub AddSheet()
    Application.ScreenUpdating = False
    Dim bottomG As Long
    bottomG = Range("G" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim ws As Worksheet
    If Sheets("All Accounts").FilterMode Then Sheets("All Accounts").ShowAllData
    For Each c In Range("G2:G" & bottomG)
        If c <> "" Then
            Set ws = Nothing
            On Error Resume Next
            Set ws = Worksheets(c.Value)
            On Error GoTo 0
            If ws Is Nothing Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
                Sheets("All Accounts").Range("A1:N" & bottomG).AutoFilter Field:=7, Criteria1:=c
                Sheets("All Accounts").Range("A1:N" & bottomG).SpecialCells(xlCellTypeVisible).EntireRow.Copy ActiveSheet.Cells(1, 1)
                ActiveSheet.Columns.AutoFit
            Else
                Sheets(c.Value).UsedRange.ClearContents
                Sheets("All Accounts").Range("A1:N" & bottomG).AutoFilter Field:=7, Criteria1:=c
                Sheets("All Accounts").Range("A1:N" & bottomG).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(c.Value).Cells(1, 1)
                Sheets(c.Value).Columns.AutoFit
            End If
            If Sheets("All Accounts").FilterMode Then Sheets("All Accounts").ShowAllData
        End If
    Next c
    Application.ScreenUpdating = True
 End Sub

 

[My Aswer Is This]

Try this:

Sub clearshts()
    Dim i As Long
    For i = Sheets("All Accounts").Index + 1 To Sheets.Count
        Sheets(i).Cells.ClearContents
        Sheets(i).Cells.ClearFormats
    Next
End Sub

 

[Justinian]

You would change

Sheets(i).Cells.ClearContents

to

Sheets(i).Cells.Delete

Of course it can be part of another code, the reason for testing it separately is to make sure it works without anything else interfering with it before integrating it in the code.

Put

Dim irw As Long

with the rest of the lines starting with Dim.

then put the lines below at the point that you want the sheets cleared (you can use F8 to step through your code line by line if you need to see what your code does at each line).

    For irw = Sheets("All Accounts").Index + 1 To Sheets.Count
        Sheets(irw).Cells.Delete
    Next

Please note that I only changed the variable from i to irw as you are already using, i just to make it less confusing.

Yes, ok we are getting closer! How can I get the code to leave formatting in row 1 on All Accounts alone (those are row headers I want to transfer over to each tab)? Also, how can it leave the column widths alone? When the formula you gave me clears the contents, I only want the data and cell borders to clear or in other words, when the code runs, I want each tab to have the same row 1 header that is on the All Accounts tab and to give each tab the same column widths that exist on All Accounts.