MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Massive Macro - Very Complicated !!! - Help !!!


Posted by Skooter on October 07, 2001 7:09 AM

Here’s something that will make your head explode. I would greatly appreciate anyone’s assistance with this macro. I tried to include as much pertinent information as humanly possible. So in a nutshell, I basically need a macro that will create a new excel workbook, prompt the user for a name and save it in a specific location. This new file will be reopened, often have information saved to it and closed many times (depending what macro buttons are used (if any at all). When the user selects a macro button, the macro will reopen a file that was created earlier in THAT session, coping a worksheet tab to it, saving the file and then close. So the new workbook will contain worksheets that the user wants in a separate file. The example below ONLY describes the project with 4 sheets and a total of 3 macro buttons. The actual project has MANY more sheets and buttons. For all practical purposes, if the problem below CAN BE solved, it can be replicated to the other sheets very easily.

Thanks,
Skooter =o)


PROJECT INFORMATION :
The main workbook is named: “master.xls” (workbook is protected / password = “book”
It contains four (4) worksheets (“info”,”select”,”quote”,”specs”) (all sheets are protected / passwords = “sheet”).

STEP #1
On “info” worksheet is macro button#1. - (desired instructions below)
FYI - When the excel program launches, the ONLY VISIBLE worksheet tab is “INFO”.


a) prompt the user for a file name to save their quote (.XLS format) (saving to C:\Clean Quotes\Quotes.
b) delete “sheet2” & “sheet3” (or same result, I really don’t need any tabs at all) This new file is going to have tabs saved to it later in the process.
c) Save & Close newly created workbook - I WOULD PREFER that the new file close for now. However, I will need to re-open this file several times later. FYI - There IS NO INFORMATION in this new file at this time, it’s blank.
d) Unhide worksheet “SELECT”
e) Hide worksheet “INFO” (now the only visible tab is “SELECT”.)


STEP #2
On the worksheet “select” are two (2) macro buttons (button #2 & button #3).
FYI – Now the only visible worksheet tab is “SELECT”.

Button #2 – (desired instructions below)
a) (master.xls file)
The workbook “Master.xls” must be unprotected (the password is “book”)
b) (master.xls file)
Unhide the worksheet “quote”
c) (master.xls file)
Unprotect the worksheet “quote” (the password is “sheet”)
d) (master.xls file)
Copy the entire worksheet tab “quote” from workbook “MASTER.xls”
e) (newly ??? created file)
OPEN the new workbook (created in step 1 - above)
f) (newly ??? created file)
and place (paste) it there before “sheet1” (or if you have a better idea for this, remember I really don’t need any previous worksheets (sheet1,sheet2,sheet3) when we created this file. I’d rather NOT have any blank sheets at all, if possible.
g) (newly ??? created file)
The newly pasted worksheet “quote” must be a “dead” worksheet (meaning values only). I usually highlight the entire sheet, copy, then paste special (values only). All formatting MUST remain the same.
h) (newly ??? created file)
The new workbook & worksheet MUST be password protected with a specific password such as “guessthis”
i) (newly ??? created file)
Save and Close (keeping the master.xls open)
j) (master.xls file)
re-Protect the worksheet “quote” (the password is “sheet”)
k) (master.xls file)
re-Hide the worksheet “quote”
l) (master.xls file)
re-Protect the workbook (the password is “book”)

Button #3 – (desired instructions below)
a) (master.xls file)
The workbook “Master.xls” must be unprotected (the password is “book”)
b) (master.xls file)
Unhide the worksheet “specs”
c) (master.xls file)
Unprotect the worksheet “specs” (the password is “sheet”)
d) (master.xls file)
Copy the entire worksheet tab “quote” from workbook “MASTER.xls”
e) (newly ??? created file)
OPEN the new workbook (created in step 1 - above)
f) (newly ??? created file)
and place (paste) it there before “sheet1” (or if you have a better idea for this, remember I really don’t need any previous worksheets (sheet1,sheet2,sheet3) when we created this file. I’d rather NOT have any blank sheets at all, if possible.
g) (newly ??? created file)
The newly pasted worksheet “specs” must be a “dead” worksheet (meaning values only). I usually highlight the entire sheet, copy, then paste special (values only). All formatting MUST remain the same.
h) (newly ??? created file)
The new workbook & worksheet MUST be password protected with a specific password such as “guessthis”
i) (newly ??? created file)
Save and Close (keeping the master.xls open)
j) (master.xls file)
re-Protect the worksheet “specs” (the password is “sheet”)
k) (master.xls file)
re-Hide the worksheet “specs”
l) (master.xls file)
re-Protect the workbook (the password is “book”)



Posted by Damon Ostrander on October 09, 2001 8:15 AM

Hi Skooter,

In reading your problem, I do not see anything here that is difficult. Each individual step appears to be just a few lines of VBA code. It is just that there are so many steps that it is a fairly time-consuming job and one not likely to elicit a complete answer on this forum. Is there a single step (or perhaps two or three) here that is really the major stumbling block for you?

Damon