Save Copy of Macro Workbook as new Workbook with only Worksheets and values, Same Location

I have a spreadsheet application that includes many macros, 15 visible sheets, and numerous hidden sheets. I have a macro button on the last sheet that saves a copy of this spreadsheet in the same folder where the main one is located (we call the newly saved copy "Scenario 1.xls"). However, Scenario 1.xls has all the functionality of the original workbook, macros and all obviously, and the file management can become circular if a user manipulates the macros in Scenario 1.xls. I would like to change the SaveScenario1 macro to instead save a new workbook (Scenario 1.xls) in the same location as the original, but to include only the visible worksheets and the values on them inside the new Scenario 1.xls. I would really appreciate any help in figuring this out. It seems like a fairly simple fix, but I have not been able to write a solution that does each of the things I need. I'm learning VBA and programming in general, so any extra explanations above and beyond the code itself are helpful to me. Thank you.

My Code, as it stands now, looks like this:

Please Login or Register  to view this content.

Hi,
check this file: SaveCopyAs.xls

contains:

Please Login or Register  to view this content.

Thanks for your reply and detailed comments. I think your code is getting very close, but I got an error (Error: 1004 [Application-defined or object-defined error] in procedure SaveScenario1 of Module Module1) when executing the code. When the code is done, there is the new workbook open, but it is still called "Book1." There is no "Scenario 1.xls" in the current path.

As far as what is in the new workbook, the code was successful in putting only values and formatting from the original workbook. Another component, though, is that there are many different macro buttons that a user might manipulate. I would like for all of these buttons in the new "Scenario 1.xls" to be disabled. It seems that the "Book1" created by your code was still able to manipulate the macros. Further help to achieve this and debug the saving would be greatly appreciated. Thank you again.


Grant

Hi,

changed code as I forgot that you wanted all the code in the new workbook, try:

Please Login or Register  to view this content.

I hope you have ActiveX buttons in your sheets, if not you may not be able to disable them at all.

Thanks again for your response. I'm not sure exactly what you mean when you say I "wanted all the code in the new workbook." I don't need code in the new workbook; I just want the visible worksheets with values and formatting, and I want the macro buttons to not work. I'm still getting error: 1004 on the new code you posted.

It is creating a new file in the folder, now, called "Scenario 1.xls", but still goes to the error handler. Also, since we are now saving the active workbook with a new name instead of creating a new workbook, the active workbook becomes "Scenario 1.xls" rather than remaining the original workbook. Also, the macro buttons are still working. How can I tell which type of buttons are being used? I did not make this application, I am simply trying to debug it and add some functionality.

Ok, as I cannot figure out why you have the error could you upload a sample workbook with the buttons?

Could you maybe give me an email to send it to, for security reasons?

Here is the file:
https://dropboxit.uark.edu/fetch?f=a...5b3b5807e867cb

So,.. I cannot open the file as it's corrupted.

After I repaired it I see a lot of sheets with values/formulas without formatting and unfortunately without any macros/code or buttons, and both codes posted before run without an error.

Does this file work on other computer except yours?

That's strange. I have sent it to numerous other people and they have not reported any problems. I downloaded the file off dropbox like you would and it worked fine for me.