Vba code - Copy, save closed workbook

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,724
Office Version
  1. 2010
Platform
  1. Windows
Hi, try this :​
VBA Code:
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
 
Solution

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,724
Office Version
  1. 2010
Platform
  1. Windows
A VBA demonstration for Night to next Day :​
VBA Code:
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

Board Regular
Joined
Aug 22, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
Absolutely fantastic. Sir, you are a legend and have saved me recoding 60 pieces of vba month. Thank you
 

Forum statistics

Threads
1,147,562
Messages
5,741,848
Members
423,691
Latest member
Fahad987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top