using command button instead of edit function

[Guest]

i have compiled a workbook that includes four worksheets. the work book is a
timesheet.when i have entered eveything into the workbook i need to to
seperate sheet11(timesheet) and save it. i do this by Edid/Move or copy
sheet. on the dropdown menu i select new book and i check the box marked
create copy.this works fine and i am able to save the copied sheet.this seems
a long way round.could i do this with a command button and vba code
thakyou john tempest

 

[Guest]

something like this should work

Dim wkbk As Workbook, sh As Worksheet
' might not need the 'array' - i copied/edited code that i am using currently
Worksheets(Array("Sheet11")).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

hth!

J

 

[Guest]

GIXXER
thankyou your code works perfect, but each timesheet must have an individual
name,with your code each timesheet would be called newtimesheet.in my
workbook i have an array on the entry sheet which by entering a clock number
brings up an indivduals name.could this be incorporated into the code so each
sheet is saved under an indviduals name by an entry on the timesheet
am i getting to complicated for my own good
thankyou john tempest

 

[Guest]

wkbk.SaveAs "c:\temp\timesheet - " & sheets("mysheet").range("A1") & ".xls"

this will save it in c:\temp
with the filename 'timesheet - <value in mysheet!A1>.xls

hth

J

 

[Guest]

GIXXER
this is the code i have now used but get runtime error 9
Private Sub CommandButton1_Click()
Dim wkbk As Workbook, sh As Worksheet
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\timesheet - " & Sheets("mysheet").Range("ENTRY!C6")
& ".xls"
wkbk.Close

 

[Guest]

try

wkbk.SaveAs "c:\temp\timesheet - " & Sheets("ENTRY").Range("C6") & ".xls"

(or change "ENTRY" to the name of the correct sheet)

 

[Guest]

THANK YOU THE CODE NOW WORKS PERFECTLY. THE CODE I USED WAS:
Private Sub CommandButton1_Click()
Dim wkbk As Workbook, sh As Worksheet
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\timesheet - " & Sheets("TIMESHEET").Range("B5") & ".xls"
wkbk.Close
End Sub

 

[Guest]

welcome! but extend your thanks to the real guru's here that taught me how
to do this! =)

J