| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting unwanted data by macro or... | Excel Discussion (Misc queries) | |||
| How do I delete unwanted extra pages on Excel worksheets | Excel Discussion (Misc queries) | |||
| Delete unwanted page breaks. | Excel Worksheet Functions | |||
| How do i delete a macro in Excel 2003 when delete isn't highlight | Excel Discussion (Misc queries) | |||
| Delete Unwanted Data | Excel Discussion (Misc queries) | |||
Hybrid Mode
