Pause to pick variable folder when saving a dated spreadsheets

[heathb]

Hi girls and guys
Am running the below code, however, I would like to be able to pause, so that the user can pick the current month, rather than have to modify the macro on the 1st of each month

I am aware this is possible, but have not been able to find previous request for help.

 Dim savedate As Variant   
   savedate = Range("ck4").Value
ndate = Range("cl4").Value
   savedate = Mid(ndate, 1, 2) & "_" & Mid(ndate, 4, 2) & "_" & Mid(ndate, 7, 4)
               
  Sheets("Import").Select
  
 Application.ScreenUpdating = True
 
   ActiveWorkbook.SaveAs Filename:="S:\COE\_Private\Work\July17\Exit check\" & "Raw  Positions" & " " & savedate & ".xlsm", CreateBackup:=False

As usual, will appreciate any and all help anyone can give

Thanks

[mdmackillop]

If ndate is a proper date then

savedate = Format(ndate, "dd_mm_yyyy")

Consider saving though as "yyyy_mm_dd" which will show in Explorer in date order.

[SamT]

Const MainPath As String = "S:\COE\_Private\Work\"
Const SubPath As String = "\Exit check\" 

Dim savedate As String 
Dim MnthPath As String

savedate = Format(Range("cl4"), "mm_dd_yyyy")
MnthFldr = Format(Date, "MMMMyy") 
 
ActiveWorkbook.SaveAs Filename:=MainPath & MnthPath & SubPath & "Raw  Positions " & savedate & ".xlsm", CreateBackup:=False

Please note: Saving files and Folders with the Month or Day first, will result in your files and folders listed in a non chronological order. IE

April15
April16
April17
August15
August16
August17
February15
February16
February17
Etc

01_01_2015 (Jan 01)
01_01_2016
01_01_2017
...
01_02_2015 (Jan 02)
01_02_2016
01_02_2017

To list the items in chronological order, always place the year before the month before the date before the Day

savedate = Format(Range("cl4"), "yyyy_mm_dd, DDDD")
MnthFldr = Format(Date, "yyyy-mm")

[heathb]

Hi, my apologies, from below, i want to be able to change the date of the folder we are saving into (Jul17 needs to be Aug17 next month), not the "savedate" code.
Sorry for the confusion

S:\COE\_Private\Work\July17\Exit check

[mdmackillop]

Using an Inputbox to confirm

ndate = Date
nmonth = Month(Date) 
savedate = Format(ndate, "dd_mm_yyyy")
Sheets("Import").Select
Application.ScreenUpdating = True
SaveMonth = InputBox("Change month number if not " & MonthName(nmonth), "Save Month", nmonth)
SaveMonth = MonthName(SaveMonth) & Format(Date, "yy")
ActiveWorkbook.SaveAs FileName:="S:\COE\_Private\Work\" & SaveMonth & "\Exit check\" & "Raw  Positions" & " " & savedate & ".xlsm", CreateBackup:=False

[SamT]

Dim MnthFldr As String 
MnthFldr = Format(Date, "MMMMyy"
"S:\COE\_Private\" & MnthFldr & "\July17\Exit check"

[mdmackillop]

Hi Sam
You forgot to delete July.

[SamT]

[heathb]

many thanks both, both ideal solutions.

Dim MnthFldr As String 
MnthFldr = Format(Date, "MMMMyy"
"S:\COE\_Private\" & MnthFldr & "\July17\Exit check"

Using an Inputbox to confirm

ndate = Date
nmonth = Month(Date) 
savedate = Format(ndate, "dd_mm_yyyy")
Sheets("Import").Select
Application.ScreenUpdating = True
SaveMonth = InputBox("Change month number if not " & MonthName(nmonth), "Save Month", nmonth)
SaveMonth = MonthName(SaveMonth) & Format(Date, "yy")
ActiveWorkbook.SaveAs FileName:="S:\COE\_Private\Work\" & SaveMonth & "\Exit check\" & "Raw  Positions" & " " & savedate & ".xlsm", CreateBackup:=False