use command button instead of edit

[john tempest]

i have created a work book that contains four sheets. the workbook is a timesheet.after everything is entered in the workbook i need to seperate the timesheet for e mailing. at the moment i do this by Edit/Move or copy sheet. on the drop down menu i select New Book and i select the check box to create copy.this creates a copy of the timesheet page which i can then e mail.this works perfectly but seems to be a long way round.could i use vba and a commsand button to do the same?
thankyou john tempest

 

[Norie]

John

Yes you could.

Worskheets("timesheet").Copy

ActiveWorkbook.SendMail Recipients:="somemail@somewhere.com;someothermail@somewhereelse", Subject:="My Subject"

 

[jss1234]

Yes - the easiest way to get the code to do this is to record a macro and perform these4 actions. This will then spped up your time. Creat the button from the forms toolbar and right click. Then assign macro to the one you just created.

 

[john tempest]

thankyou jarrod but i think i might have given you the wrong information.i need to seperate the worksheet and then save it to file before emailing as there is more than one timesheet to complete.i normally end up with twenty timesheets in the folder before e mailing.just to copy the sheet using a command button would surfice ,then i can save it to a folder
thankyou john tempest

 

[Norie]

John

Jarrod's suggestion is actually a very good one.

Though it won't immediately give you a solution it will definitely give you (and us if you need more help) something to work with.

 

[john tempest]

i seem to be getting closer to what i want, the following code works and saves the sheet,but what i need to do is save individually named sheets.
Dim wkbk As Workbook, sh As Worksheet
' might not need the 'array' - i copied/edited code that i am using currently
Worksheets("TIMESHEET").Copy
Set wkbk = ActiveWorkbook
' get rid of all cell formulas
For Each sh In wkbk.Worksheets
With sh.UsedRange
.Value = .Value
End With
Next
wkbk.SaveAs "c:\temp\newtimesheet.xls"
wkbk.Close
in my work book on the entry sheet i have an array which when i insert a clock number into a cell an individual name appears, could i incorporate this into the code
this seems to be getting complicated
thankyou john tempest

 

[Norie]

John

Where will the sheet names be coming from?

 

[john tempest]

the names will becoming from cell C6 from the worksheet called entry
thankyou john tempest