Comman Button to Save Worksheet as Workbook

  • Thread starter Thread starter tmstreet
  • Start date Start date
T

tmstreet

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.
 
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.
 
Back
Top