PDA

View Full Version : Solved: Save Sheet to a user defined location



Sreeja
06-19-2007, 10:44 PM
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
06-19-2007, 11:34 PM
Assuming that you want all sheets saved to the same folder and using the sheet tab name as the new filename,


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

sujittalukde
06-20-2007, 12:00 AM
Also posted here
http://www.ozgrid.com/forum/showthread.php?p=368341#post368341

Bob Phillips
06-20-2007, 12:42 AM
You can use FileDialog to get the folder rather that GetOpenFilename and stripping the filename



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

Sreeja
06-20-2007, 04:01 AM
That worked :)