Search all worksheets in a workbook...

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

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

---
Regards,
Norman



"Peter" <Peter@discussions.microsoft.com> wrote in message
news:FD9C6D5A-4527-43D6-900B-1CADECDBDD7D@microsoft.com...
> 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

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

"Norman Jones" wrote:

> 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
>
> ---
> Regards,
> Norman
>
>
>
> "Peter" <Peter@discussions.microsoft.com> wrote in message
> news:FD9C6D5A-4527-43D6-900B-1CADECDBDD7D@microsoft.com...
> > 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
>
>
>

Hi Peter,

> This seems to work well, but it only works for page 1.

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.

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.


---
Regards,
Norman



"Peter" <Peter@discussions.microsoft.com> wrote in message
news:647B4AA6-2763-4E06-B7A8-73F76BFAED8B@microsoft.com...
> 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
>
> "Norman Jones" wrote:
>
>> 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
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Peter" <Peter@discussions.microsoft.com> wrote in message
>> news:FD9C6D5A-4527-43D6-900B-1CADECDBDD7D@microsoft.com...
>> > 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
>>
>>
>>

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

"Norman Jones" wrote:

> Hi Peter,
>
> > This seems to work well, but it only works for page 1.
>
> 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.
>
> 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.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Peter" <Peter@discussions.microsoft.com> wrote in message
> news:647B4AA6-2763-4E06-B7A8-73F76BFAED8B@microsoft.com...
> > 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
> >
> > "Norman Jones" wrote:
> >
> >> 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
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Peter" <Peter@discussions.microsoft.com> wrote in message
> >> news:FD9C6D5A-4527-43D6-900B-1CADECDBDD7D@microsoft.com...
> >> > 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
> >>
> >>
> >>
>
>
>

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.


---
Regards,
Norman



"Peter" <Peter@discussions.microsoft.com> wrote in message
news:A3145D40-02D8-4317-BFA4-6CF3EA770283@microsoft.com...
> 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
>
> "Norman Jones" wrote:
>
>> Hi Peter,
>>
>> > This seems to work well, but it only works for page 1.
>>
>> 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.
>>
>> 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.
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Peter" <Peter@discussions.microsoft.com> wrote in message
>> news:647B4AA6-2763-4E06-B7A8-73F76BFAED8B@microsoft.com...
>> > 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
>> >
>> > "Norman Jones" wrote:
>> >
>> >> 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
>> >>
>> >> ---
>> >> Regards,
>> >> Norman
>> >>
>> >>
>> >>
>> >> "Peter" <Peter@discussions.microsoft.com> wrote in message
>> >> news:FD9C6D5A-4527-43D6-900B-1CADECDBDD7D@microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>

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


"Norman Jones" wrote:

> 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.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Peter" <Peter@discussions.microsoft.com> wrote in message
> news:A3145D40-02D8-4317-BFA4-6CF3EA770283@microsoft.com...
> > 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
> >
> > "Norman Jones" wrote:
> >
> >> Hi Peter,
> >>
> >> > This seems to work well, but it only works for page 1.
> >>
> >> 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.
> >>
> >> 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.
> >>
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Peter" <Peter@discussions.microsoft.com> wrote in message
> >> news:647B4AA6-2763-4E06-B7A8-73F76BFAED8B@microsoft.com...
> >> > 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
> >> >
> >> > "Norman Jones" wrote:
> >> >
> >> >> 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
> >> >>
> >> >> ---
> >> >> Regards,
> >> >> Norman
> >> >>
> >> >>
> >> >>
> >> >> "Peter" <Peter@discussions.microsoft.com> wrote in message
> >> >> news:FD9C6D5A-4527-43D6-900B-1CADECDBDD7D@microsoft.com...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>