Macro to copy a workbook and delete a specific worksheet
 

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

Macro to copy a workbook and delete a specific worksheet
 

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


On 23 Mar, 21:11, dgd1212 wrote:
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

Macro to copy a workbook and delete a specific worksheet
 

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

Macro to copy a workbook and delete a specific worksheet
 

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

Macro to copy a workbook and delete a specific worksheet
 

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

Macro to copy a workbook and delete a specific worksheet
 

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