Save as New Workbook, Let User specify Folder. Name file using cell value and todays date.

[Melimob]

Hi there

I thought I found what I needed (almost).

I want to save the current file as
1) new workbook
2) user should specify their file path folder
3) name of file should read from Sheet11.Range("C9") (which is also a named range if easier in case it moves) =Table13[Country] + "MOVE Template" + Todays date (which I currently have in a cell)
4) AND - I need to remove the formulas from the file except on a couple of pages/ranges.

Below is my code (without the removal of formulas as I don't know how to tackle that) but I am getting an error?

Sub SaveFileAsCountry()
Dim sPath As String


' Open the file dialog
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show


If .SelectedItems.Count > 0 Then
sPath = .SelectedItems(1)
ThisWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & Sheet11.Range("C9").Value & "_" & "MOVE_Template" & "_" & Sheet11.Range("F1").Value & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Range("B1").Select
End If
End With
End Sub

Many thanks in advance!

 

[Melimob]

WARNING!! DO NOT USE BELOW CODE - HAS CORRPUPTED MY WORKBOOK WHICH I'm DESPERATELY TRYING TO SAVE...

Hi there

I thought I found what I needed (almost).

I want to save the current file as
1) new workbook
2) user should specify their file path folder
3) name of file should read from Sheet11.Range("C9") (which is also a named range if easier in case it moves) =Table13[Country] + "MOVE Template" + Todays date (which I currently have in a cell)
4) AND - I need to remove the formulas from the file except on a couple of pages/ranges.

Below is my code (without the removal of formulas as I don't know how to tackle that) but I am getting an error?

Sub SaveFileAsCountry()
Dim sPath As String


' Open the file dialog
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show


If .SelectedItems.Count > 0 Then
sPath = .SelectedItems(1)
ThisWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & Sheet11.Range("C9").Value & "_" & "MOVE_Template" & "_" & Sheet11.Range("F1").Value & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Range("B1").Select
End If
End With
End Sub

Many thanks in advance!