For Existing and New files: CREATE & UPDATE file on different USB drives

[aacod]

I have following drives on my PC, ‘C’ and ‘D’ as internal drives, ‘H’, ‘G’, ‘I ‘and ‘J’ as USB external drives.

I have some common files with same names on USB drives with main file on Drive H and copies on other USB drives.

I update the main file(s) daily on USB drive ‘H’.

I need a VBA code or a macro to do the following:

When I create a new file ‘my text’ on Drive ‘H’, it should create an exact copy of same file on Drive ‘G’, ‘I’ and ‘J’.

The path of the file created on H:\folder 1\folder 2\folder 3\mytext file and the paths on the USB drives would be the same except the change in the USB drive letter.

Also when I update the main file on ‘H’, files on all USB drives ‘G’, ‘I’ and ‘J’ must get updated on hitting ’SAVE’ and/or on Closing the main file.

Thanks.

[SamT]

This has no error checking, so if a drive fails or is just unplugged, you'll get an error.


Option Explicit


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)
SamT
End Sub

Sub SamT()
Dim X As String
With Me
X = Mid(.FullName, 2)
.SaveCopyAs "G" & X
.SaveCopyAs "I" & X
.SaveCopyAs "J" & X
End With
End Sub

[aacod]

Hey SamT, GREAT, Works Perfect.

I will keep this thread open as I may need to add some more functionality.

Thanks.

aacod

[aacod]

Hi SamT,

Can this VBA be tweaked to do the following:

If I create a new file either on drive C, D, G, H or I, it would save a copy on drive G in a folder labeled 'BACKUP' and update that file each time I make alteration to the original file.

Would appreciate your help.

Thanks.

aacod

[SamT]

Do you have a Personal.xls* file. That is a special workbook created by Excel the first time that you save a Recorded Macro to it. Personal.xls* opens every time you open Excel and can work on any other file. By adding some path checking it could save a file created on those drive to all the other drives. Its default Excel mode is hidden, but it's always available to VBA. I use it to keep code snippets and often used Macros in. It also backs itself up every time it's Saved.

I'm not that familiar with the code requirements to do it in the Personal book. Check back in a few hours.

I need some details. Obiously you want to save many books to G, H, I, and J. but do you want to save copies of all books that way?

Do you want to NOT save books to C or D?

[aacod]

Hi SamT,

Correct me if I am wrong. So I can copy the code in post #2 in a new excel workbook, save it as 'Personal.xlsm" and use this every time I need to create different new excel file. Also where do I need to paste the VBA code? In one of the workbook sheet or a module?

Thanks.

aacod.

[SamT]

Please stand corrected :D

Record a dummy macro using the Tools >> Macro menu. In the "Store Macro In" dropdown, select Personal Macro book. That will create the specially modified Personal.xlsm for you. Then it will require a bunch of new code to have Personal.xlsm save other books for you. It might be easier for you to create a Template book with the code above in it. That would just require some IF...Thens to check the path of the active book.