Hello, I have a workbook that has been emailed to about 300 users that, due to circumstances out of my control, I need to
update. What I would like to do is email each user a workbook that contains a macro that will copy four sheets from the
original workbook and replace the sheets in the new workbook that are named the same. Due to my very rudimentary coding, I
have been unable to overcome some issues buy scabbing together bits and pieces of code gleaned through searching the
1. I don't know what the original workbook has been renamed to, so I will need a dialog box to allow the user to select it.
From my searches the code below seems to be what I need to use.
2. Both workbooks will contain the exact same named ranges and formats. I believe if the macro will use the
pastespecial>Values command it should not affect the named ranges or formats.
3. I would like to rename the new
workbook with the exact name of the old workbook and saved in the same location as the original. Then I need to append the
old workbook name with "old-do not use". If this is not possible, then deleting the old workbook is fine as
4. The workbooks are full of confidential sales information so I cannot post them online. However the
sheets that need to be copied and replaced are "Sheet4" through "Sheet7"
5. Ideally, this
macro would run the first time the workbook has been opened and not run again. However, if that requires a lot of extra code
then I can assign it to a button.
Thank you for any assistance you may provide.
Here are the two
files. I am trying to move the sheets listed above from the
file ending with "no sales" to the file ending with "blank"
Updates for clarification are in red.