Macro to copy a workbook and delete a specific worksheet

[dgd1212]

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

 

[Jarek Kujawa]

You might try to record those actions and then play with the code

 

[Ron de Bruin]

Do you want to make a copy of the activeworkbook or
is the workbook not open ?

 

[dgd1212]

A copy of the activeworkbook, which is open.

Thanks

 

[Ron de Bruin]

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

 

[Ron de Bruin]

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