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

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!

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 !!!

VB Code:

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

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