Typo
With wb2
'There is no error check if the sheet exist or if it is the only sheet in the workbook
Application.DisplayAlerts = False
.Sheets("Sheet1").Delete
Application.DisplayAlerts = False
.Close SaveChanges:=True
End With
The second DisplayAlerts line must be True instead of False
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Ron de Bruin" wrote in message ...
This example make a copy of the activeworkbook (that workbook must be saved ones)
and delete a sheet named "Sheet1"
Sub Copy_Workbook_And_Delete_Sheet()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Set wb1 = ActiveWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'If you want to change the file name then change only TempFileName
TempFilePath = Application.DefaultFilePath & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
With wb2
'There is no error check if the sheet exist or if it is the only sheet in the workbook
Application.DisplayAlerts = False
.Sheets("Sheet1").Delete
Application.DisplayAlerts = False
.Close SaveChanges:=True
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"dgd1212" wrote in message ...
A copy of the activeworkbook, which is open.
Thanks
"Ron de Bruin" wrote:
Do you want to make a copy of the activeworkbook or
is the workbook not open ?
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"dgd1212" wrote in message ...
How would one write a macro that makes a copy of a workbook and places it in
MyDocuments Folder? The macro also needs to delete one worksheet.
Thanks