Comman Button to Save Worksheet as Workbook

OK, I would like to thank you all for making this such a valuable
resource.

Here is my question.

I need a macro that when I click on a command button it will save the
current worksheet (not the workbook) as a file based on the contents of
2 cells.

For example.

WorkSheet = 8

c9 = Name

d3 = Date

When you click on the button it will save Just the current worksheet
(8) as Name_Date.xls

Any help is greatly appreciated.

Thanks,

-T

I'd use a button from the forms toolbar that's assigned to this macro:

Option Explicit
Sub testme()

Dim NewName As String

With ActiveSheet
NewName = .Range("c9").Value & Format(.Range("d3"), "yyyymmdd") _
& ".xls"
.Copy 'to a new workbook
End With

With ActiveSheet.Parent 'that new workbook
.SaveAs Filename:=ThisWorkbook.Path & "\" & NewName, _
FileFormat:=xlWorkbookNormal
.Close savechanges:=False
End With

End Sub

Remember that the workbook name can't have those /'s in them--that's why I used
format.



tmstreet wrote:
>
> OK, I would like to thank you all for making this such a valuable
> resource.
>
> Here is my question.
>
> I need a macro that when I click on a command button it will save the
> current worksheet (not the workbook) as a file based on the contents of
> 2 cells.
>
> For example.
>
> WorkSheet = 8
>
> c9 = Name
>
> d3 = Date
>
> When you click on the button it will save Just the current worksheet
> (8) as Name_Date.xls
>
> Any help is greatly appreciated.
>
> Thanks,
>
> -T

--

Dave Peterson

Thanks so much.

I can fumble my way through formulas, but I need you 'real' excel
people to figure out the rest.

Your the man Dave.

Dave Peterson wrote:
> I'd use a button from the forms toolbar that's assigned to this macro:
>
> Option Explicit
> Sub testme()
>
> Dim NewName As String
>
> With ActiveSheet
> NewName = .Range("c9").Value & Format(.Range("d3"), "yyyymmdd") _
> & ".xls"
> .Copy 'to a new workbook
> End With
>
> With ActiveSheet.Parent 'that new workbook
> .SaveAs Filename:=ThisWorkbook.Path & "\" & NewName, _
> FileFormat:=xlWorkbookNormal
> .Close savechanges:=False
> End With
>
> End Sub
>
> Remember that the workbook name can't have those /'s in them--that's why I used
> format.
>
>
>
> tmstreet wrote:
> >
> > OK, I would like to thank you all for making this such a valuable
> > resource.
> >
> > Here is my question.
> >
> > I need a macro that when I click on a command button it will save the
> > current worksheet (not the workbook) as a file based on the contents of
> > 2 cells.
> >
> > For example.
> >
> > WorkSheet = 8
> >
> > c9 = Name
> >
> > d3 = Date
> >
> > When you click on the button it will save Just the current worksheet
> > (8) as Name_Date.xls
> >
> > Any help is greatly appreciated.
> >
> > Thanks,
> >
> > -T
>
> --
>
> Dave Peterson