Macro to delete unwanted sheets

S

Sal

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.
 
G

Gary''s Student

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
 
S

Sal

I will give it a try - thanks very much

Gary''s Student said:
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
Click to expand...
 
B

Bill Kuunders

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
 
S

Sal

thank you very much

Bill Kuunders said:
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
Click to expand...
 
S

Sal

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
 
Top