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" <(E-Mail Removed)> wrote in message
news:4718BDAA-08F8-4109-BFEF-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:4718BDAA-08F8-4109-BFEF-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> > news:4718BDAA-08F8-4109-BFEF-(E-Mail Removed)...
> > >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.
> >
> >