PDA

View Full Version : Deleting empty worksheets in a workbook



ChrisCA1966
07-01-2011, 06:16 AM
Each month I run a large data file from our data base to identify manufactoring defects by serial numbers from several different locations. Once I pull down the data my workbook will have several blank worksheets that will change month to month depending on defects. Once I build my workbook my worksheets will always be in the same order "Group SNs, A1A SNs, A2A SNs, A3A SNs, and so on". What I'm trying to accomplish is, for example location A2A work sheet is empty this month, Once I start running my macro it will need start with the first worksheet in order beginning with Group SNs, next to A1A, once it reaches A2A reconiges it's a blank sheet, deletes and move onto A3A.

Below is the current macro I'm using in combination with another macro to run analysis in the background.

Application.Run "SN_Sort"
Sheets("Serial_Numbers").Select
Sheets("Serial_Numbers").Name = "Group SNs"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Page1-2").Select
ActiveWindow.SelectedSheets.Delete

Sheets("A1A").Select
Application.Run "SN_Sort"
Sheets("Serial_Numbers").Select
Sheets("Serial_Numbers").Name = "A1A SNs"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("A1A").Select
ActiveWindow.SelectedSheets.Delete


Sheets("A2A").Select
Application.Run "SN_Sort"
Sheets("Serial_Numbers").Select
Sheets("Serial_Numbers").Name = "A2A SNs"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("A2A").Select
ActiveWindow.SelectedSheets.Delete


Sheets("A3A").Select
Application.Run "SN_Sort"
Sheets("Serial_Numbers").Select
Sheets("Serial_Numbers").Name = "A3A SNs"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("A3A").Select
ActiveWindow.SelectedSheets.Delete





End Sub

Bob Phillips
07-01-2011, 06:28 AM
Try something like this



If Application.CountA(ActiveSheet.Cells) = 0 Then

ActiveSheet.Delete
End If