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 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.