Hello,
I would like to create a macro that searches a workbook to determine how many worksheets contain data. It will delete the empty worksheets and record in an array, the position of the active worksheets.
So far I'm getting stuck on the delete function
"A workbook must contain at least one visible worksheet"
Any help would be great. Thanks!
Sub Find_worksheets()
Dim shtNext As Worksheet
Dim WorkSheetArray(0 To 9)
worksheetnumber = ActiveWorkbook.Worksheets.Count
MsgBox "There are " & worksheetnumber & " Worksheets"
For Each shtNext In ActiveWorkbook.Worksheets
With shtNext.Range("AD2")
If .Value > 0 Then
WorkSheetArray(shtNext) = shtNext
Else
ActiveWorkbook.Worksheets.Delete
End If
End With
Next shtNext
End Sub
I would like to create a macro that searches a workbook to determine how many worksheets contain data. It will delete the empty worksheets and record in an array, the position of the active worksheets.
So far I'm getting stuck on the delete function
"A workbook must contain at least one visible worksheet"
Any help would be great. Thanks!
Sub Find_worksheets()
Dim shtNext As Worksheet
Dim WorkSheetArray(0 To 9)
worksheetnumber = ActiveWorkbook.Worksheets.Count
MsgBox "There are " & worksheetnumber & " Worksheets"
For Each shtNext In ActiveWorkbook.Worksheets
With shtNext.Range("AD2")
If .Value > 0 Then
WorkSheetArray(shtNext) = shtNext
Else
ActiveWorkbook.Worksheets.Delete
End If
End With
Next shtNext
End Sub