delete sheet help 2

[RANDY IN NC]

Sorry about the last post... when I submited I got an error.. and I cannot figure out how to get it out of here..

So I will try again..

I have the following macro. The section that deletes empty sheets works fine in another book as a standalone macro. but I cannot get it to work in this larger appliction. What am I missing..

Thank you

Sub emailsubmit()
'
' emailsubmit Macro
' Macro recorded 03/22/2006 by Randy
'

'
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets(Array("ORDER", "order2")).Select
Sheets("ORDER").Activate
Cells.Select
Selection.Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then
sh.Delete
End If
Next

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="excel Files (*.xls), *.xls")
If fileSaveName <> False Then
ActiveWorkbook.SaveAs fileSaveName
End If
Application.DisplayAlerts = False
Windows("orders for email.xls").Activate
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

[Don Guillett]

try using the same for/next loop as with the delete. Also try to get rid of
your selections. And, try to not do ALL cells. Do only those needed by
finding the last row and column.

--
Don Guillett
SalesAid Software
[email protected]
"RANDY IN NC" <[email protected]>
wrote in message
news:[email protected]...
>
> Sorry about the last post... when I submited I got an error.. and I
> cannot figure out how to get it out of here..
>
> So I will try again..
>
> I have the following macro. The section that deletes empty sheets works
> fine in another book as a standalone macro. but I cannot get it to work
> in this larger appliction. What am I missing..
>
> Thank you
>
> Sub emailsubmit()
> '
> ' emailsubmit Macro
> ' Macro recorded 03/22/2006 by Randy
> '
>
> '
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
> Sheets(Array("ORDER", "order2")).Select
> Sheets("ORDER").Activate
> Cells.Select
> Selection.Copy
> Workbooks.Add
> Range("A1").Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=False
> Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
>
> Dim sh As Worksheet
> For Each sh In ThisWorkbook.Worksheets
> If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then
> sh.Delete
> End If
> Next
>
> fileSaveName = Application.GetSaveAsFilename( _
> fileFilter:="excel Files (*.xls), *.xls")
> If fileSaveName <> False Then
> ActiveWorkbook.SaveAs fileSaveName
> End If
> Application.DisplayAlerts = False
> Windows("orders for email.xls").Activate
> ActiveWorkbook.Close
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
> End Sub
>
>
> --
> RANDY IN NC
> ------------------------------------------------------------------------
> RANDY IN NC's Profile:
> http://www.excelforum.com/member.php...o&userid=29778
> View this thread: http://www.excelforum.com/showthread...hreadid=525467
>