Copying command buttons

[oldjay]

I want to copy a sheet and its command buttons to a new sheet named for the
present date i.e.(11/24/09). Buttons created from the Toolbox in Excel 2003

oldjay

 

[OssieMac]

Hi Oldjay,

Note when copying a worksheet with the below method then the command buttons
with their associated VBA code will be copied with them. Therefore it is a
good idea to place most of the code in a standard module and just call the
code from the command buttons. That way you are not duplicating code
throughout your workbook and if you need to modifiy the code then it only
needs to be done in the one place.

Also you cannot use slashes (/) in the worksheet name. I changed them to
hyphens.

Note the comments in the code.
Sub CopyWorkSheet()

Dim wsShtToCopy As Worksheet
Dim strNewShtName As String
Dim wsNewSht As Worksheet

'Edit "Sheet1" to your worksheet name
Set wsShtToCopy = Sheets("Sheet1")

'Assign proposed new worksheet name to variable
strNewShtName = Format(Date, "mm-dd-yy")

'Test for new sheet name already existing
'by attempting to assign to a variable
On Error Resume Next
Set wsNewSht = Sheets(strNewShtName)

'If error is zero then worksheet exists
If Err.Number = 0 Then 'No error
MsgBox "Worksheet " & strNewShtName _
& " already exists"
Exit Sub
Else
On Error GoTo 0 'Resume error trapping ASAP
wsShtToCopy.Copy Before:=Sheets(1)
ActiveSheet.Name = strNewShtName
End If

End Sub

 

[Peter T]

Sub test()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.Copy after:=ws

' will fail if a sheet named today's date already exits
ActiveSheet.Name = Date
End Sub

This will copy the controls and code behind the sheet, as well as contents.

Regards,
Peter T

 

[Peter T]

As OssieMac points out best ensure there are no "/" in the new sheet name
change

ActiveSheet.Name = Date

to (one way)
ActiveSheet.Name = Replace(Date, "/", "-")

(my default format is has "-" separators hence I forgot)

Regards,
Peter T

 

[OssieMac]

An afterthought and not sure of your level in VBA so it might help. You can
change the following line to the active sheet in lieu of a specific sheet.
Set wsShtToCopy = Sheets("Sheet1")

Can change to this so it works from any worksheet.
Set wsShtToCopy = ActiveSheet

Also there are other options of where the worksheet should be placed in the
workbook. Here are a couple of examples.

wsShtToCopy.Copy After:=Sheets(Sheets.Count) 'After last sheet
wsShtToCopy.Copy After:=ActiveSheet

Can use Before in lieu of After.

 

[OssieMac]

Hi Peter,

It is better to use code that will work for all users.
ActiveSheet.Name = Replace(Date, "/", "-") will fail if the user does not
have slashes in the date format.

By using the Format function you can set the sheet name with characters that
you know will not fail irrespective of the user's default date format.

 

[Peter T]

Hello OssieMac,

No that's no problem, try this -

MsgBox Replace("abc", "x", "y") ' abc

Probably I'd use the Format function. However if I wanted to distribute I
wouldn't want to prejudge if user wants dd-mm-yyyy or mm-dd-yyyy. The
Replace function is a simple way to ensure no slashes appear without
changing the order, and an as an alternative to what you had already
suggested, I did indicate. Of course there are other ways too, not least
Format.

Not sure if Date could ever be returned with some other invalid characters,
if so would need to think again.

Regards,
Peter T

 

[oldjay]

You guys have got me confused. What is the final code. I do not care about
the date format. What ever is the easiest.

oldjay

 

[OssieMac]

Hi oldjay,

If you use the original code I posted it should work under all situations.