Vba code - Copy, save closed workbook

[trevolly]

Hi all

I have made a workbook for a Daily Log to track all events on an airfield. Firstly, many thanks to all that have helped so far. The workbook is saved in date format and day / night. For example today would have two excel files in a folder named September, “12-10-21 Day” and “12-10-21 Night”. This would mean that there are 60 excel files in the “September” folder.

I have a code that copies a Handover sheet from the current shifts excel workbook to the next workbook (ie: Day to Night) that is closed and this works perfectly. This is great as then when the incoming shift opens the relevant workbook (daily log) they are presented with the Handover sheet and all info they need to know. The full flow is …. I click on a button in the current open “day” workbook , it opens the relevant “night” workbook, copies the sheet named “Handover” and then saves and closes the “Night” workbook, all in the background.

This is done by using a vba code:

Sub CopySheetToClosedWB()
Dim SourceSht As Worksheet
Set SourceSht = Sheets("Handover")
Application.ScreenUpdating = False

Set closedBook = Workbooks.Open("C:\Users\Trevor\September\12-10-21 Night.xlsm")
SourceSht.Copy After:=closedBook.Sheets("Closures")
closedBook.Close SaveChanges:=True

Application.ScreenUpdating = True
End Sub

This works brilliantly (thanks @JoeMo ) but it does mean I have to change the bold text part of the vba for every file. Would anyone know a way of a way around this (maybe the date part of the vba rolls on to the next entry? Or maybe the vba above references cells in the workbook that then tell the vba code the date and shift?). I know it seems a long shot but I thought I’d ask as everyone has been v helpful on these forums.

Regards Trevor.

 

[Marc L]

Hi, try this :​

Sub CopySheetToClosedWB()
        Dim Ws As Worksheet
        Set Ws = Worksheets("Handover")
        Application.ScreenUpdating = False
    With Workbooks.Open(Replace(ActiveWorkbook.FullName, " Day.", " Night."))
         Ws.Copy , .Sheets("Closures")
        .Close True
    End With
        Application.ScreenUpdating = True
        Set Ws = Nothing
End Sub

 

[trevolly]

Thanks @Marc L that's cut the vba recoding by half!! Thank you!!

Being cheeky, I don't suppose there's a code that would do the same but from a night shift to the next day? The only thing it means is that the date is going to be +1. For example the current workflow is to copy the "Handover" sheet from "12-10-21 Day" to 12-10-21 Night" is there any way to go from "12-10-21 Night" to "13-10-21 Day" for example? At the moment I use the original vba code altering the date in each "night" workbook telling it to look for the next day.

 

[Marc L]

A VBA demonstration for Night to next Day :​

Sub Demo1()
        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

 

[trevolly]

Absolutely fantastic. Sir, you are a legend and have saved me recoding 60 pieces of vba month. Thank you