Complicated Macro - Help !

Posted by Skooter on October 04, 2001 5:30 PM

I need help writing a macro to do the following. I am able to write some of it but CAN NOT find the right combination for the ENTIRE macro.- Help Please !!

1) While workbook ORIGINAL is open. By clicking Sub Button1 (macro button) a new workbook is created and prompts the user for a FILE NAME (.XLS format). Hopefully saving to a specific (already created) folder such as C:\CC Pro\CC Data. Or at least defaulting there.

2) Next copy entire worksheet tab SHEET1 from workbook ORIGINAL to new Workbook (created in step 1)

3) I need to make the copied SHEET1 in the new workbook a dead worksheet (meaning values only). I usually highlight the entire sheet, copy, then paste special (values only). All formatting MUST remain the same.

4) The new workbook & worksheet MUST be password protected with a specific password such as guessthis.

5) Close the newly created workbook. While leaving the ORIGINAL workbook open.

* Note: the ORIGINAL workbook must remain open at all times during this process.

Thanks,
Skooter

Posted by anon on October 04, 2001 6:25 PM

Dim theFile As String
theFile = Application.InputBox("Enter the new file name")
Workbooks.Add.SaveAs Filename:="C:\My Documents\" & theFile & ".xls"
Workbooks("Original.xls").Sheets("Sheet1").Copy Before:=Workbooks(theFile & ".xls").Sheets(1)
With Workbooks(theFile & ".xls").Sheets(1).Cells
.Copy
.PasteSpecial Paste:=xlValues
End With
With Workbooks(theFile & ".xls")
.Sheets(1).Protect password:="guessthis"
.Close saveChanges:=True
End With

Posted by Richard S on October 04, 2001 6:51 PM

also format

Shouldn't you include the following before End With. He wants the format as well.

.PasteSpecial Paste:=xlFormats

Richard

Posted by Anon on October 04, 2001 9:12 PM

Re: also format

No, because the whole sheet was copied into the new workbook.

Posted by Skooter on October 05, 2001 12:58 PM

Re: Thank-you !!!

Thanks a million Anon & Richard..... It works awesome....

Skooter