There is a list of sheet name under column B of sheet "Date", I would like to
delete any sheets, which name is not included within the lists and the Date
sheet cannot be deleted too. Do you have any suggestions on how to code a
macro to do it?
Thanks in advance for any suggestions
Eric

Eric,

Try this

Sub del_sheets()
Dim ws As Worksheet
Set sht = Sheets("Date")
Dim LastRow As Long
LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & LastRow)
Application.DisplayAlerts = False
For Each c In MyRange
For Each ws In ThisWorkbook.Worksheets
If ws.Name < "Date" Then
If IsError(Application.Match(ws.Name, MyRange, 0)) Then
ws.Delete
End If
End If
Next
Next
Application.DisplayAlerts = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Eric" wrote:

There is a list of sheet name under column B of sheet "Date", I would like to
delete any sheets, which name is not included within the lists and the Date
sheet cannot be deleted too. Do you have any suggestions on how to code a
macro to do it?
Thanks in advance for any suggestions
Eric

There are values under column B
1 in cell B1 and 2 in cell B2,
There are sheet named under 1 and 2 too.
When I run following macro, sheet 1 and 2 are deleted, but the names are
listed under column B, do you have any suggestions on how to fix it?
Thank you very much for any suggestions
Eric

"Mike H" wrote:

Eric,

Try this

Sub del_sheets()
Dim ws As Worksheet
Set sht = Sheets("Date")
Dim LastRow As Long
LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & LastRow)
Application.DisplayAlerts = False
For Each c In MyRange
For Each ws In ThisWorkbook.Worksheets
If ws.Name < "Date" Then
If IsError(Application.Match(ws.Name, MyRange, 0)) Then
ws.Delete
End If
End If
Next
Next
Application.DisplayAlerts = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Eric" wrote:

There is a list of sheet name under column B of sheet "Date", I would like to
delete any sheets, which name is not included within the lists and the Date
sheet cannot be deleted too. Do you have any suggestions on how to code a
macro to do it?
Thanks in advance for any suggestions
Eric

Eric,

There are values under column B
1 in cell B1 and 2 in cell B2,
There are sheet named under 1 and 2 too.

You never told us that. Change this line so it starts in b3

Set MyRange = Range("B3:B" & LastRow)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Eric" wrote:

There are values under column B
1 in cell B1 and 2 in cell B2,
There are sheet named under 1 and 2 too.
When I run following macro, sheet 1 and 2 are deleted, but the names are
listed under column B, do you have any suggestions on how to fix it?
Thank you very much for any suggestions
Eric

"Mike H" wrote:

Eric,

Try this

Sub del_sheets()
Dim ws As Worksheet
Set sht = Sheets("Date")
Dim LastRow As Long
LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & LastRow)
Application.DisplayAlerts = False
For Each c In MyRange
For Each ws In ThisWorkbook.Worksheets
If ws.Name < "Date" Then
If IsError(Application.Match(ws.Name, MyRange, 0)) Then
ws.Delete
End If
End If
Next
Next
Application.DisplayAlerts = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Eric" wrote:

There is a list of sheet name under column B of sheet "Date", I would like to
delete any sheets, which name is not included within the lists and the Date
sheet cannot be deleted too. Do you have any suggestions on how to code a
macro to do it?
Thanks in advance for any suggestions
Eric

eric,

My previous post won't work. Try this instead

Note the code now contains this line

S = "Date,1,2"

This is a list of all sheets you don't want deleted in addition to the list
in column B

Sub del_sheets()
Dim ws As Worksheet
Set sht = Sheets("Date")
Dim LastRow As Long
S = "Date,1,2"
V = Split(S, ",")
LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & LastRow)
Application.DisplayAlerts = False
For Each c In MyRange
For Each ws In ThisWorkbook.Worksheets
If IsError(Application.Match(ws.Name, MyRange, 0)) _
And IsError(Application.Match(ws.Name, V, 0)) Then
ws.Delete
End If

Next
Next
Application.DisplayAlerts = True
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Eric,

There are values under column B
1 in cell B1 and 2 in cell B2,
There are sheet named under 1 and 2 too.

You never told us that. Change this line so it starts in b3

Set MyRange = Range("B3:B" & LastRow)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Eric" wrote:

There are values under column B
1 in cell B1 and 2 in cell B2,
There are sheet named under 1 and 2 too.
When I run following macro, sheet 1 and 2 are deleted, but the names are
listed under column B, do you have any suggestions on how to fix it?
Thank you very much for any suggestions
Eric

"Mike H" wrote:

Eric,

Try this

Sub del_sheets()
Dim ws As Worksheet
Set sht = Sheets("Date")
Dim LastRow As Long
LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & LastRow)
Application.DisplayAlerts = False
For Each c In MyRange
For Each ws In ThisWorkbook.Worksheets
If ws.Name < "Date" Then
If IsError(Application.Match(ws.Name, MyRange, 0)) Then
ws.Delete
End If
End If
Next
Next
Application.DisplayAlerts = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Eric" wrote:

There is a list of sheet name under column B of sheet "Date", I would like to
delete any sheets, which name is not included within the lists and the Date
sheet cannot be deleted too. Do you have any suggestions on how to code a
macro to do it?
Thanks in advance for any suggestions
Eric

Under column B, each cell contains any sheetname or nothing, but the name is
a variable, which can be changed based on different inputs.

For example,
The following sheets are existed as shown below:
Date, Peter, Mary, School, Apple, Car, Cup

There are names starting from cell B1 to cell B5 under Sheet 'Date' as shown
below: cell B6 to B500 are empty cell, which contain "" sign.
Peter, Mary, School, Apple, Car

Each sheet named under those names should be kept and will not be deleted,
if there is another sheet 'Cup', you do not find Cup under column B in Date
sheet, then sheet 'Cup' will be deleted.

Therefore, the following sheets will be maintained.
Date, Peter, Mary, School, Apple, Car

Do you have any suggestions on how to fix it? but not hard code those names
within macro.

Thank you very much for any suggestions
Eric

"Mike H" wrote:

eric,

My previous post won't work. Try this instead

Note the code now contains this line

S = "Date,1,2"

This is a list of all sheets you don't want deleted in addition to the list
in column B

Sub del_sheets()
Dim ws As Worksheet
Set sht = Sheets("Date")
Dim LastRow As Long
S = "Date,1,2"
V = Split(S, ",")
LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & LastRow)
Application.DisplayAlerts = False
For Each c In MyRange
For Each ws In ThisWorkbook.Worksheets
If IsError(Application.Match(ws.Name, MyRange, 0)) _
And IsError(Application.Match(ws.Name, V, 0)) Then
ws.Delete
End If

Next
Next
Application.DisplayAlerts = True
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Eric,

There are values under column B
1 in cell B1 and 2 in cell B2,
There are sheet named under 1 and 2 too.

You never told us that. Change this line so it starts in b3

Set MyRange = Range("B3:B" & LastRow)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Eric" wrote:

There are values under column B
1 in cell B1 and 2 in cell B2,
There are sheet named under 1 and 2 too.
When I run following macro, sheet 1 and 2 are deleted, but the names are
listed under column B, do you have any suggestions on how to fix it?
Thank you very much for any suggestions
Eric

"Mike H" wrote:

Eric,

Try this

Sub del_sheets()
Dim ws As Worksheet
Set sht = Sheets("Date")
Dim LastRow As Long
LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B1:B" & LastRow)
Application.DisplayAlerts = False
For Each c In MyRange
For Each ws In ThisWorkbook.Worksheets
If ws.Name < "Date" Then
If IsError(Application.Match(ws.Name, MyRange, 0)) Then
ws.Delete
End If
End If
Next
Next
Application.DisplayAlerts = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Eric" wrote:

There is a list of sheet name under column B of sheet "Date", I would like to
delete any sheets, which name is not included within the lists and the Date
sheet cannot be deleted too. Do you have any suggestions on how to code a
macro to do it?
Thanks in advance for any suggestions
Eric