Search all worksheets in a workbook...

P

Peter

Hello All,

I am trying to figure out how to search a certain cell range (AA4), on each
of upto 30 worksheets in the same workbook. Then if it finds that range to be
empty (ie: the page has not been used) delete it when exiting.

My workbook autosaves a file on exit using the same cell range on the first
worksheet as it's filename.

Regards
Peter
 
N

Norman Jones

Hi Peter,

Try:

Sub sTester()
Dim sh As Worksheet
Const sTestCellAddress As String = "AA4"

On Error GoTo XIT
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
With sh
If IsEmpty(.Range(sTestCellAddress )) Then
.Delete
End If
End With
Next sh

XIT:

Application.DisplayAlerts = True

End Sub
 
P

Peter

Hi Norman,
Thanks for the reply..
This seems to work well, but it only works for page 1. If there is data on
page 2 or more it saves the whole book still.

This is the code I use when exiting Excel to autosave the workbook.

Private Sub Workbook_BeforeClose(cancel As Boolean)

Dim savepath As String
savepath = "c:\company\invoice\"
ActiveWorkbook.SaveAs Filename:=savepath & Range("AA4").Value & ".xls"

End Sub

I can't see why it's not working. I tried to integrate your code with this,
but with no further success.

Regards
Peter
 
N

Norman Jones

Hi Peter,
This seems to work well, but it only works for page 1.
Click to expand...

Are you sure? In testing, all sheets whose AA4 cell is empty are deleted.
The exception to this would be where none of the worksheets has a populated
AA4 cell, In this case the last worksheet would be retained as a workbook is
required to have a minimum of one worksheet.
I can't see why it's not working. I tried to integrate your code with
this,
but with no further success.
Click to expand...

Put the suggested macro a normal module of the workbook (not in the
ThisWorkbook module or the worksheet modules). Then amend the
Workbook_BeforeClose macro to call the suggested macro, e.g.:

Private Sub Workbook_BeforeClose(cancel As Boolean)
Dim savepath As String

savepath = "C:\Company\Invoice\"

sTester

Me.SaveAs Filename:=savepath _
& Range("AA4").Value & ".xls"
End Sub

You may wish to change the name of the suggested macro from Sub STester()
to (say) Sub SheetsDelete(). If you change the macro name, change sTester
line in the Workbook_BeforeClose routine to accord with the amended name.
 
P

Peter

Sorry Norman, my bad.

I think the problem is the worksheets are, and in my case, need to be
protected.
Is there a way of removing the protection, doing the deletion, then
re-adding the protection to the pages that remain?

Regards
Peter
 
N

Norman Jones

Hi Peter,

Try this version,

Sub sTester()
Dim sh As Worksheet
Const sTestCellAddress As String = "AA4"

On Error GoTo XIT

Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
With sh
If IsEmpty(.Range(sTestCellAddress)) Then
sh.Unprotect password:="OpenSaysMe"
.Delete
End If
End With
Next sh

XIT:

Application.DisplayAlerts = True

End Sub


In the above, change "OpenSaysMe" to your used password.

As before, change the name of the routine to suit.
 
P

Peter

Thanks Norman, that solved the problem.

Well after I realised it was the Workbook I had to unprotect and not the
Worksheet it was.

Thankyou again.

Regards
Peter
 
Top