I need a macro to copy a certain sheet to a new workbook, unprotect the
sheet, and copy and paste the values in the new worksheet as values (Paste
Special). The one problem I am having is the first step: copying the sheet to
a new workbook. If you can give me some sample code for this whole process,
that would be great. I would more than likely use a Command Button from the
Control Toolbox on another sheet so that when I email the new workbook, there
are no macros in it.

Thanks in advance.
--
I am running on Excel 2003, unless otherwise stated.

Hi Orion

See
http://www.rondebruin.nl/mail/folder1/mail2.htm

Add this before the commented code that make the values
and remove the ' for every commented line

Looks like this then

Destwb.Sheets(1).Unprotect "password"

' 'Change all cells in the worksheet to values if you want
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Orion Cochrane" wrote in message
...
I need a macro to copy a certain sheet to a new workbook, unprotect the
sheet, and copy and paste the values in the new worksheet as values (Paste
Special). The one problem I am having is the first step: copying the sheet to
a new workbook. If you can give me some sample code for this whole process,
that would be great. I would more than likely use a Command Button from the
Control Toolbox on another sheet so that when I email the new workbook, there
are no macros in it.

Thanks in advance.
--
I am running on Excel 2003, unless otherwise stated.

Thanks, Ron. I should have stated that I manually email the workbook because
I have Yahoo! Mail at home. I just need a macro to copy the sheet to a new
workbook. I'll take the code you gave me in this post and stick it at the
end. Thanks again.
--
I am running on Excel 2003, unless otherwise stated.


"Ron de Bruin" wrote:

Hi Orion

See
http://www.rondebruin.nl/mail/folder1/mail2.htm

Add this before the commented code that make the values
and remove the ' for every commented line

Looks like this then

Destwb.Sheets(1).Unprotect "password"

' 'Change all cells in the worksheet to values if you want
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Orion Cochrane" wrote in message
...
I need a macro to copy a certain sheet to a new workbook, unprotect the
sheet, and copy and paste the values in the new worksheet as values (Paste
Special). The one problem I am having is the first step: copying the sheet to
a new workbook. If you can give me some sample code for this whole process,
that would be great. I would more than likely use a Command Button from the
Control Toolbox on another sheet so that when I email the new workbook, there
are no macros in it.

Thanks in advance.
--
I am running on Excel 2003, unless otherwise stated.

I see the macro I need. Thanks. I'll have to get back to you later to test it
at home.
--
I am running on Excel 2003, unless otherwise stated.


"Orion Cochrane" wrote:

Thanks, Ron. I should have stated that I manually email the workbook because
I have Yahoo! Mail at home. I just need a macro to copy the sheet to a new
workbook. I'll take the code you gave me in this post and stick it at the
end. Thanks again.
--
I am running on Excel 2003, unless otherwise stated.


"Ron de Bruin" wrote:

Hi Orion

See
http://www.rondebruin.nl/mail/folder1/mail2.htm

Add this before the commented code that make the values
and remove the ' for every commented line

Looks like this then

Destwb.Sheets(1).Unprotect "password"

' 'Change all cells in the worksheet to values if you want
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Orion Cochrane" wrote in message
...
I need a macro to copy a certain sheet to a new workbook, unprotect the
sheet, and copy and paste the values in the new worksheet as values (Paste
Special). The one problem I am having is the first step: copying the sheet to
a new workbook. If you can give me some sample code for this whole process,
that would be great. I would more than likely use a Command Button from the
Control Toolbox on another sheet so that when I email the new workbook, there
are no macros in it.

Thanks in advance.
--
I am running on Excel 2003, unless otherwise stated.