Macro to delete unwanted sheets
 

Hi there,
I am creating a questionnaire type form which has a separate sheet for
different subjects, each sheet has a rating total at the bottom. I am
looking for a macro which will scan the entire workbook and delete those
sheets that have a total of 0 at the bottom.

Also these figures are consolidated into a final worksheet and I would like
to be able to delete any columns that relate to the deleted workbooks above.

Many thanks.

Macro to delete unwanted sheets
 

Lets say that the rating total is in cell A20 in each sheet. Then the
following macro is delete all sheets that have zero in that cell:


Sub rmsheet()
k = Sheets.Count
Application.DisplayAlerts = False
For i = k To 1 Step -1
If Sheets(i).Range("A20") = 0 Then
Sheets(i).Delete
End If
Next
Application.DisplayAlerts = True
End Sub

--
Gary''s Student
gsnu200710


"Sal" wrote:

Hi there,
I am creating a questionnaire type form which has a separate sheet for
different subjects, each sheet has a rating total at the bottom. I am
looking for a macro which will scan the entire workbook and delete those
sheets that have a total of 0 at the bottom.

Also these figures are consolidated into a final worksheet and I would like
to be able to delete any columns that relate to the deleted workbooks above.

Many thanks.

Macro to delete unwanted sheets
 

I will give it a try - thanks very much

"Gary''s Student" wrote:

Lets say that the rating total is in cell A20 in each sheet. Then the
following macro is delete all sheets that have zero in that cell:


Sub rmsheet()
k = Sheets.Count
Application.DisplayAlerts = False
For i = k To 1 Step -1
If Sheets(i).Range("A20") = 0 Then
Sheets(i).Delete
End If
Next
Application.DisplayAlerts = True
End Sub

--
Gary''s Student
gsnu200710


"Sal" wrote:

Hi there,
I am creating a questionnaire type form which has a separate sheet for
different subjects, each sheet has a rating total at the bottom. I am
looking for a macro which will scan the entire workbook and delete those
sheets that have a total of 0 at the bottom.

Also these figures are consolidated into a final worksheet and I would like
to be able to delete any columns that relate to the deleted workbooks above.

Many thanks.

Macro to delete unwanted sheets
 

And this one will also delete the column

Greetings from New Zealand


Sub deletesheet()
Dim number As Integer
Dim coldelete As Integer
coldelete = -1
number = 2 'you can change this starting number to suit
'my first sheets' result was in column "D" of sheet1
Sheets("sheet1").Select 'assuming that the summary is on sheet1
Application.DisplayAlerts = False
For Each Sheet In Sheets
Sheet.Select
number = number + 1
If Range("A6").Value = 0 Then 'change this range to suit
ActiveSheet.Delete
coldelete = coldelete + 1
Sheets("sheet1").Select
Columns(number - coldelete).Delete
End If
Next
Application.DisplayAlerts = True
Sheets("sheet1").Select
End Sub

"Sal" wrote in message
...
I will give it a try - thanks very much

"Gary''s Student" wrote:

Lets say that the rating total is in cell A20 in each sheet. Then the
following macro is delete all sheets that have zero in that cell:


Sub rmsheet()
k = Sheets.Count
Application.DisplayAlerts = False
For i = k To 1 Step -1
If Sheets(i).Range("A20") = 0 Then
Sheets(i).Delete
End If
Next
Application.DisplayAlerts = True
End Sub

--
Gary''s Student
gsnu200710


"Sal" wrote:

Hi there,
I am creating a questionnaire type form which has a separate sheet for
different subjects, each sheet has a rating total at the bottom. I am
looking for a macro which will scan the entire workbook and delete
those
sheets that have a total of 0 at the bottom.

Also these figures are consolidated into a final worksheet and I would
like
to be able to delete any columns that relate to the deleted workbooks
above.

Many thanks.

Macro to delete unwanted sheets
 

thank you very much

"Bill Kuunders" wrote:

And this one will also delete the column

Greetings from New Zealand


Sub deletesheet()
Dim number As Integer
Dim coldelete As Integer
coldelete = -1
number = 2 'you can change this starting number to suit
'my first sheets' result was in column "D" of sheet1
Sheets("sheet1").Select 'assuming that the summary is on sheet1
Application.DisplayAlerts = False
For Each Sheet In Sheets
Sheet.Select
number = number + 1
If Range("A6").Value = 0 Then 'change this range to suit
ActiveSheet.Delete
coldelete = coldelete + 1
Sheets("sheet1").Select
Columns(number - coldelete).Delete
End If
Next
Application.DisplayAlerts = True
Sheets("sheet1").Select
End Sub

"Sal" wrote in message
...
I will give it a try - thanks very much

"Gary''s Student" wrote:

Lets say that the rating total is in cell A20 in each sheet. Then the
following macro is delete all sheets that have zero in that cell:


Sub rmsheet()
k = Sheets.Count
Application.DisplayAlerts = False
For i = k To 1 Step -1
If Sheets(i).Range("A20") = 0 Then
Sheets(i).Delete
End If
Next
Application.DisplayAlerts = True
End Sub

--
Gary''s Student
gsnu200710


"Sal" wrote:

Hi there,
I am creating a questionnaire type form which has a separate sheet for
different subjects, each sheet has a rating total at the bottom. I am
looking for a macro which will scan the entire workbook and delete
those
sheets that have a total of 0 at the bottom.

Also these figures are consolidated into a final worksheet and I would
like
to be able to delete any columns that relate to the deleted workbooks
above.

Many thanks.

Macro to delete unwanted sheets
 

Hi Gary,

I tested this out but the code fell over at line:
Sheets(i).Delete

Dont really know enough about VBA to work out the problem.

Cheers



"Gary''s Student" wrote:

Lets say that the rating total is in cell A20 in each sheet. Then the
following macro is delete all sheets that have zero in that cell:


Sub rmsheet()
k = Sheets.Count
Application.DisplayAlerts = False
For i = k To 1 Step -1
If Sheets(i).Range("A20") = 0 Then
Sheets(i).Delete
End If
Next
Application.DisplayAlerts = True
End Sub

--
Gary''s Student
gsnu200710


"Sal" wrote:

Hi there,
I am creating a questionnaire type form which has a separate sheet for
different subjects, each sheet has a rating total at the bottom. I am
looking for a macro which will scan the entire workbook and delete those
sheets that have a total of 0 at the bottom.

Also these figures are consolidated into a final worksheet and I would like
to be able to delete any columns that relate to the deleted workbooks above.

Many thanks.