use vba to create copies of excel workbook for different users

  • Hi there,

    I have an excel workbook that serves as a master workbook.
    I have to create 30 copies of this workbook, 1 for each site. Each workbook will have small differences in the data held. This is because I will refresh the sheet before saving it.
    There is a dropdown list on the 1st tab of the workbook that contains the names of the sites.
    The sequense, I suppose would be like:

    • select the next name on the dropdown list
    • "calculate" the spreadsheet to enable the refresh
    • some sort of "save-as" with the name from the dropdown list forming the name of the file. The Master file must be kept intact.
    • select the next name from the dropdown list
    • calculate the sheet .....
    • ...and so on

    and I hope this makes sense!!!
    I have a few other things I will add to the script like Protect Sheet etc.

    Thanks for your help

  • Re: use vba to create copies of excel workbook for different users

    One way you can do it is to record a macro, (assuming you can't write it), of what needs to be done for each individual copy and save it as a module within the Master workbook. Within the macro it should also include the saving each copy as it goes along (it's possible to e-mail it out automatically as well if you wish).

    To keep the Master workbook intact you will need to use "ActiveWorkbook.Saved = True" & "Application.Quit" before the end of the macro. The true statement makes excel "think" it has been saved before closing.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!