Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub SaveWorksheetsAsXLSX()
- Dim WS As Excel.Worksheet
- Dim SaveToDirectory As String
- Dim CurrentWorkbook As String
- Dim CurrentFormat As Long
- CurrentWorkbook = ThisWorkbook.FullName
- CurrentFormat = ThisWorkbook.FileFormat
- ' Store current details for the workbook
- SaveToDirectory = "\\tempest-dc01\data\Clients\Synectics\Customer Excellence 2017\Output\Verbatims - Cleaned\"
- For Each WS In ThisWorkbook.Worksheets
- Sheets(WS.Name).Copy
- ActiveWorkbook.SaveAs Filename:=SaveToDirectory & "Synectics Customer Excellence 2017 Verbatims " & "- " & WS.Name & ".xlsx", FileFormat:=51
- ActiveWorkbook.Close savechanges:=False
- ThisWorkbook.Activate
- Next
- Application.DisplayAlerts = False
- ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
- Application.DisplayAlerts = True
- ' Temporarily turn alerts off to prevent the user being prompted
- ' about overwriting the original file.
- End Sub
Add Comment
Please, Sign In to add comment