delete sheet help 2
 

Sorry about the last post... when I submited I got an error.. and
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 work
fine in another book as a standalone macro. but I cannot get it to wor
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 Su

--
RANDY IN N
-----------------------------------------------------------------------
RANDY IN NC's Profile: http://www.excelforum.com/member.php...fo&userid=2977
View this thread: http://www.excelforum.com/showthread.php?threadid=52546

delete sheet help 2
 

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

"RANDY IN NC"
wrote in message
...

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