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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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