Open 2nd workbook,, copy information accross and then close both workbooks

[JamesT1]

Good Morning All


I have 2 workbooks.... Central GDP Training and Databackup

The Central file if the main one for all the information.... Before the file closes it should be opening the backup file,, copying the information over then closing the backup before closing itself

I am using this to copy information from several worksheets in to the backup file... when the SaveBackup is assigned to a button its working well and doing as it should.

However when I want it to run automatically before the Central file closes I am receiving an error message for Subscript Out of Range

Could someone please point ou where I am going wrong

Many thanks

Jim

Please Login or Register  to view this content.

[Mumps1]

If you want to automatically copy the ranges to the backup file when you close the Training file, place this macro in the code module for ThisWorkbook in the Training file. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. When you close the Training file, the ranges to the backup file will be copied over and both files will close.

Please Login or Register  to view this content.

[Marc L]

Another ThisWorkbook module VBA event procedure :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Saved Then Save
    SaveCopyAs "H:\Administration\GDP SOP Training\Data Backup\Databackup.xlsm"
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Marc L]

A ThisWorkbook module VBA event variation :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Not Saved Then Save
   With Application
       .DisplayAlerts = False
       .ScreenUpdating = False
        Sheets("Data").Copy
        Sheets("Lists").Copy , ActiveWorkbook.Sheets(1)
        Sheets("Archive").Copy , ActiveWorkbook.Sheets(2)
        Sheets("ListsArchive").Copy , ActiveWorkbook.Sheets(3)
        ActiveWorkbook.SaveAs "H:\Administration\GDP SOP Training\Data Backup\Databackup", 51
        ActiveWorkbook.Close
       .DisplayAlerts = False
       .ScreenUpdating = True
   End With
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[JamesT1]

Marc L

Many thanks,,, this works

Jim