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
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