Code to save in specific folder

[trevolly]

Good afternoon all

I have a workbook made that we use as a daily log at work. The kind members at MrExcel wrote some vba code for me that at the end of a shift, when a button is pressed runs and opens the next shifts log copies a "Handover sheet" from the current log to the next shifts log and then saves and closes the next shifts log, all in the background. This works very well. The workbooks / logs are saved in a folder named after the month (October, November ect...) and the files are named in date format... 01-10-21 Day, 01-10-21 Night etc......all in the same monthly folder.

I was wondering is there a way that when I get to the end of the month (say in October 31-10-21 Night) the coding can do the same process - open, copy and save - but save in to the next months folder (November for example). The folder address would be \Daily Log\October (or Daily Log\November).

The vba coding Im using to go from Night shift to Day shift is....

Sub CopySheetNighttoDay()
'Handover Copy Night to Day
Dim S$(), V, Ws As Worksheet
With ActiveWorkbook
S = Split(.Name, " Night.")
V = Split(S(0), "-"): If UBound(V) <> 2 Or UBound(S) <> 1 Then Beep: Exit Sub
S(0) = .Path & "\" & Format(DateSerial(2000 + V(2), V(1), V(0) + 1), "dd-mm-yy") & " Day." & S(1)
If Dir(S(0)) = "" Then Beep: Exit Sub
Set Ws = .Worksheets("Handover")
End With
Application.ScreenUpdating = False
With Workbooks.Open(S(0))
Ws.Copy , .Sheets("Closures")
.Close True
End With
Application.ScreenUpdating = True
Set Ws = Nothing
End Sub

Thanks all
T

 

[Marc L]

Hi,​

​

replace the codeline S(0) = .Path & "\" & Format(DateSerial(2000 + V(2), V(1), V(0) + 1), "dd-mm-yy") & " Day." & S(1) with​

​

V = DateSerial(2000 + V(2), V(1), V(0) + 1)
S(0) = Left(.Path, InStrRev(.Path, "\")) & Format(V, "mmmm") & "\" & Format(V, "dd-mm-yy") & " Day." & S(1)

 

[trevolly]

Wow it works great! Once again @Marc L thank you so, so much. Also thank you for pointing out I didn't use the "vba brackets" in my original post. Sorry all. Promise I will next time

 

[trevolly]

@Marc L - can I ask what is it in this line of code that makes the code look in a specific place / folder? I am trying to learn and also if I wanted to change it to other months. Thnks

V = DateSerial(2000 + V(2), V(1), V(0) + 1)
S(0) = Left(.Path, InStrRev(.Path, "\")) & Format(V, "mmmm") & "\" & Format(V, "dd-mm-yy") & " Day." & S(1)

 

[Marc L]

​

From the beginning until the last "\" (or before the last Format) …​

 

[trevolly]

Ok thanks - I'm off to look up the DateSerial command!

 

[trevolly]

Could I please ask what the "V" is in the code? I've watched so many dateserial videos but no one seems to explain all the variants of dateserial. I cant work out what I'd change to go from Nov to Dec, Dec to Jan 2022 ect...

 

[Marc L]

​

V = is a Variant variable as you can read in the VBA help of DateSerial returning a Variant date …​

​

The array elements used in this DateSerial function come from the previous allocation of the same variable V via the Split function​

like you can easily check & follow during the execution within the VBE Locals window.​

 

[trevolly]

Hi all.

Hope you are all well. I’d really appreciate any help as I think this will be the last change to my project that I’m creating for my employer.

So my employer has changed the path for the folders that the files are located in. Instead of it being “\Daily Log\November” for example they now would like to use “\Daily Log\ 11 NOV 2021” (for example). Could anyone please help with the alteration to the coding I originally asked help with?

@Marc L managed to change the original code to copy a worksheet from a workbook in “\Daily Log\October\31-10-21 Night” to a workbook located in “\Daily Log\November\01-11-21 Day” and then save the workbook and close it.