use command button instead of edit

john tempest

Board Regular
Joined
Nov 20, 2005
Messages
51
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
John

Yes you could.
Code:
Worskheets("timesheet").Copy

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

jss1234

Active Member
Joined
May 28, 2004
Messages
434
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

Board Regular
Joined
Nov 20, 2005
Messages
51
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 20, 2005
Messages
51
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
John

Where will the sheet names be coming from?
 

john tempest

Board Regular
Joined
Nov 20, 2005
Messages
51
the names will becoming from cell C6 from the worksheet called entry
thankyou john tempest
 

Watch MrExcel Video

Forum statistics

Threads
1,118,088
Messages
5,570,147
Members
412,306
Latest member
fabio6
Top