using command button instead of edit function
 

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

using command button instead of edit function
 

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

"john tempest" wrote:

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

using command button instead of edit function
 

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


"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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

using command button instead of edit function
 

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

"john tempest" wrote:

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


"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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

using command button instead of edit function
 

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

"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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


"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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

using command button instead of edit function
 

try

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

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


"john tempest" wrote:

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

"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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


"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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

using command button instead of edit function
 

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


"Gixxer_J_97" wrote:

try

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

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


"john tempest" wrote:

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

"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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


"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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

using command button instead of edit function
 

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

J


"john tempest" wrote:

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


"Gixxer_J_97" wrote:

try

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

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


"john tempest" wrote:

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

"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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


"Gixxer_J_97" wrote:

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

"john tempest" wrote:

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