help with save as function....please!!

[jritter]

I have an excel with multiple worksheets, on each sheet I want to place a button that, when pressed, saves that sheet to a new file. Anyone know how to do this, using saveas, so that the user can input where the file is saved and what the name is.


Thanks!

[NeedSomeAnswers]

I dont think that the SaveAs function will allow you to save an individual worksheet into a new workbook, however it can be done with a bit of fiddling around.

Paste the Code into a module in your workbook!

The following example assumes that you have a Button called "commandbutton1" & a textbox called "TxtFileName" on your worksheet.

The Code below first takes a copy of the activeworksheet and then pastes it into a new workbook.

Then it loops through the activeworkbooks and trys to find any newly created, i.e - any Workbooks name "Book.." something.

The first one it finds it Saves it with the filename entered by the user in the txtbox & closes it.

Obviously this does rely on you not having any other unsaved workbooks open while you are running it, or any saved workbooks open who's names start with "Book" !!!.

Maybe you could modify it to work better !!!


Sub CommandButton1_Click()

If Len(TxtFileName) > 0 Then
SaveSheetToNewWorkbook
End If

End Sub
Sub SaveSheetToNewWorkbook()

Dim n As Integer
Dim blnsave As Boolean
Dim strSavePath As String
Dim strFileName As String

strSavePath = "C:\Temp\"
strFileName = TxtFileName

With Application
.ActiveSheet.Select
.ActiveSheet.Copy ' - Copies the worksheet into a new workbook
'.ActiveSheet.Move ' - Moves the worksheet into a new workbook

For n = 1 To .Workbooks.Count
If Left(.Workbooks.Item(n).Name, 4) = "Book" Then
.Workbooks.Item(.Workbooks.Item(n).Name).SaveAs (strSavePath & strFileName)
.Workbooks.Item(n).Close
End If
Next n
End With

End Sub

[agleinmiller]

instead of a text box for inputting the new filename, you could use the GetSaveAsFilename method.