Is it possible to have a button on a worksheet which
would output an excel file or a word file with just that
sheets information on it?

A lot of the cells contain formaulas.

Also how can I get the button to call the file by the
worksheet tab name

Maybe something like:

Option Explicit
Sub testme01()

Dim newWks As Worksheet

ActiveSheet.Copy 'to a new workbook
Set newWks = ActiveSheet 'in that new workbook

With newWks
.Buttons.Delete
.Parent.SaveAs Filename:=ThisWorkbook.Path & "\" & .Name & ".xls", _
FileFormat:=xlWorkbookNormal
.Parent.Close savechanges:=False
End With
End Sub



Pete wrote:

Is it possible to have a button on a worksheet which
would output an excel file or a word file with just that
sheets information on it?

A lot of the cells contain formaulas.

Also how can I get the button to call the file by the
worksheet tab name

--

Dave Peterson

Dave,

It is great, the filename layout works just right. The
sheet contains formulas though and they are linked. I
need to be able to send the file by email to someone
without security access to the main file. The values
need to be there and not an option to update values.

Thanks.

Pete

-----Original Message-----
Maybe something like:

Option Explicit
Sub testme01()

Dim newWks As Worksheet

ActiveSheet.Copy 'to a new workbook
Set newWks = ActiveSheet 'in that new workbook

With newWks
.Buttons.Delete
.Parent.SaveAs Filename:=ThisWorkbook.Path & "\"
& .Name & ".xls", _
FileFormat:=xlWorkbookNormal
.Parent.Close savechanges:=False
End With
End Sub



Pete wrote:

Is it possible to have a button on a worksheet which
would output an excel file or a word file with just
that
sheets information on it?

A lot of the cells contain formaulas.

Also how can I get the button to call the file by the
worksheet tab name

--

Dave Peterson
.

You could do a copy|paste special|values (in code) or something like this

Option Explicit
Sub testme01()

Dim newWks As Worksheet

ActiveSheet.Copy 'to a new workbook
Set newWks = ActiveSheet 'in that new workbook

With newWks
.Buttons.Delete
With .UsedRange
.Value = .Value
End With
.Parent.SaveAs Filename:=ThisWorkbook.Path & "\" & .Name & ".xls", _
FileFormat:=xlWorkbookNormal
.Parent.Close savechanges:=False
End With
End Sub

With .UsedRange
.Value = .Value
End With

could be:

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues

Depending on how you feel <bg.


Pete wrote:

Dave,

It is great, the filename layout works just right. The
sheet contains formulas though and they are linked. I
need to be able to send the file by email to someone
without security access to the main file. The values
need to be there and not an option to update values.

Thanks.

Pete

-----Original Message-----
Maybe something like:

Option Explicit
Sub testme01()

Dim newWks As Worksheet

ActiveSheet.Copy 'to a new workbook
Set newWks = ActiveSheet 'in that new workbook

With newWks
.Buttons.Delete
.Parent.SaveAs Filename:=ThisWorkbook.Path & "\"
& .Name & ".xls", _
FileFormat:=xlWorkbookNormal
.Parent.Close savechanges:=False
End With
End Sub



Pete wrote:

Is it possible to have a button on a worksheet which
would output an excel file or a word file with just
that
sheets information on it?

A lot of the cells contain formaulas.

Also how can I get the button to call the file by the
worksheet tab name

--

Dave Peterson
.


--

Dave Peterson