View Full Version : [SOLVED:] Pause to pick variable folder when saving a dated spreadsheets
heathb
06-30-2017, 07:58 AM
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
06-30-2017, 08:26 AM
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.
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
07-03-2017, 03:40 AM
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
07-03-2017, 05:09 AM
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
Dim MnthFldr As String
MnthFldr = Format(Date, "MMMMyy"
"S:\COE\_Private\" & MnthFldr & "\July17\Exit check"
mdmackillop
07-03-2017, 07:00 AM
Hi Sam
You forgot to delete July.:devil2:
heathb
07-03-2017, 09:04 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.