Macro to save file to specific location with a specific file naming convention

fishep6

New Member
Joined
Feb 10, 2014
Messages
43
Hi
I
have looked through the forums and can find answers to part of my question but not in full and when I have tried to join the various elements together it wont work.

Effectively I want to do the following

Name the file using the naming convention set in cell B1 of the sheet named 'review form'
I then want this to create a pop up which will prompt the user to save the file at a location of their choosing

so effectively the automated part will select the file name and also save it as an xlsx rather than xlsm

I have tried combining the following but it just wont work.

a question I have is the workbook has several hidden sheets which I need to remain hidden or actually can be removed when saving if this is an option.

The sheet I need to use 'review form' has the file naming convention in cell B1, It is also a protected sheet with only certain cells editable so I am not sure whether this may affect anything? Finally there are formulas in this sheet that I will need to remain there once saved, again not sure this makes a difference.

Thank you :LOL:

Sub SaveAs()
Dim SaveName As String
SaveName = ActiveSheet.Range("b2").Text
ActiveWorkbook.SaveAs Filename:="E:\User\JoeDoe" & _
SaveName & ".xls"
End Sub
Sub Macro2()
Dim flder As FileDialog
Dim foldername As String
Set flder = Application.FileDialog(msoFileDialogFolderPicker)
With flder
.Title = "Select the folder containing data"
.AllowMultiSelect = True
If .Show <> -1 Then GoTo NextCode
foldername = .SelectedItems(1)
End With
NextCode:
GetFolder = foldername
Set flder = Nothing
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
When you are using the dialog box, you are just getting the string for the name of the file to save you would still require a saveas line

`ActiveWorkbook.SaveAs Filename:=FolderName & FileName`
 

fishep6

New Member
Joined
Feb 10, 2014
Messages
43
When you are using the dialog box, you are just getting the string for the name of the file to save you would still require a saveas line

`ActiveWorkbook.SaveAs Filename:=FolderName & FileName`

I have tried this but still coming up with an error, all I want is the naming convention to be what is written in cell B1 and once the macro button is pressed for it to open up the box as if you were manually to save the file, so that you then have to choose the location to save it in manually, but I need the file name to be in the format that is written in cell B2

Thanks

Sub SaveAs()
Dim SaveName As String
SaveName = ActiveSheet.Range("b2").Text
ActiveWorkbook.SaveAs Filename:=foldername & Filename &
SaveName & ".xls"
Dim flder As FileDialog
Set flder = Application.FileDialog(msoFileDialogFolderPicker)
With flder
.Title = "Select the folder containing data"
.AllowMultiSelect = True
If .Show <> -1 Then GoTo NextCode
foldername = .SelectedItems(1)
End With
NextCode:
GetFolder = foldername
Set flder = Nothing
End Sub
 

Forum statistics

Threads
1,136,338
Messages
5,675,187
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top