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

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

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

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

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

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