Solved: Save Sheet to a user defined location

[Sreeja]

Hi,
I have a workbook with 6 worksheets, I want to save each sheet to different folder. The folder will be specified by the user at the run time.

[geekgirlau]

Assuming that you want all sheets saved to the same folder and using the sheet tab name as the new filename,

[VBA]
Sub SaveSheets()
Dim wks As Worksheet
Dim varFolder As Variant


' capture the new file location
varFolder = Application.GetSaveAsFilename()

' only proceed if the user did not cancel
If varFolder <> False Then
' loop thru the sheets
For Each wks In ActiveWorkbook.Worksheets
wks.Copy

ActiveWorkbook.SaveAs Left(varFolder, InStrRev(varFolder, "\")) & wks.Name
ActiveWorkbook.Close False
Next wks
End If
End Sub

[/VBA]

[sujittalukde]

Also posted here
http://www.ozgrid.com/forum/showthre...341#post368341

[Bob Phillips]

You can use FileDialog to get the folder rather that GetOpenFilename and stripping the filename

[vba]

Sub SaveSheets()
Dim wks As Worksheet
Dim varFolder As Variant


' capture the new file location
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = -1 Then
varFolder = .SelectedItems(1)

For Each wks In ActiveWorkbook.Worksheets
wks.Copy

ActiveWorkbook.SaveAs varFolder & "\" & wks.Name
ActiveWorkbook.Close False
Next wks
End If
End With
End Sub
[/vba]

[Sreeja]

That worked