Macro to copy sheet(s)

Every month, I update a workbook with 35 sheets that I have to break it won
for 17 people, the sheets that I send varies for everyone (1 to 17 sheets),
the process I use is: create a copy of the sheet(s) as a new workbook, what
I am looking for is to have the sheet(s) selected and run the macro to do
the work (I would do it with a button).
I tries to use the macro recorder, but unfortunately comes with the sheet
name.
Could somebody help me to get this task automated.

Thanks

Add a sheet to your workbook called "Start". Place a button on it from the
Control Toolbox. Right click on the button to view the properties and change
the button caption. Right click on the button and select view code. Paste
this code in.

Private Sub CommandButton1_Click()
Dim wks As Worksheet

For Each wks In Worksheets
If wks.Name <> "Start" Then wks.Copy 'Copies all but the start Sheet
Next wks
End Sub
--
HTH...

Jim Thomlinson


"FGOMEZ" wrote:

> Every month, I update a workbook with 35 sheets that I have to break it won
> for 17 people, the sheets that I send varies for everyone (1 to 17 sheets),
> the process I use is: create a copy of the sheet(s) as a new workbook, what
> I am looking for is to have the sheet(s) selected and run the macro to do
> the work (I would do it with a button).
> I tries to use the macro recorder, but unfortunately comes with the sheet
> name.
> Could somebody help me to get this task automated.
>
> Thanks
>
>
>
>

Let's say you install a log on device for all users. Based on who is logged
in, the workbook automatically reveals hidden sheets you grant them access to
via a hidden table. Everyone gets the same workbook. They log on and
automatically can only see what they are supposed to. You save yourself a lot
of work once you set it up initially. If it's just a matter of convenience,
they only need user names; if you need security as well, then they need
passwords too.

Sheets(x).Visible = -1 'reveal a sheet
Sheets(x).Visible = 2 ' very hidden

"FGOMEZ" wrote:

> Every month, I update a workbook with 35 sheets that I have to break it won
> for 17 people, the sheets that I send varies for everyone (1 to 17 sheets),
> the process I use is: create a copy of the sheet(s) as a new workbook, what
> I am looking for is to have the sheet(s) selected and run the macro to do
> the work (I would do it with a button).
> I tries to use the macro recorder, but unfortunately comes with the sheet
> name.
> Could somebody help me to get this task automated.
>
> Thanks
>
>
>
>

Roy,
Thanks for your response, it sounds very interesting but this concept is
totally new for me, when you say install a log on device for all users, what
do you mean by that, how do I install that, also I understand how to hide
sheets, but again not a clue of how to do it by user.
Maybe you can explain a little bit more to me.

Thanks


"Roy" <Roy@discussions.microsoft.com> wrote in message
news:18657501-B250-4D2A-BCC8-7E4D05AFDFFC@microsoft.com...
> Let's say you install a log on device for all users. Based on who is
logged
> in, the workbook automatically reveals hidden sheets you grant them access
to
> via a hidden table. Everyone gets the same workbook. They log on and
> automatically can only see what they are supposed to. You save yourself a
lot
> of work once you set it up initially. If it's just a matter of
convenience,
> they only need user names; if you need security as well, then they need
> passwords too.
>
> Sheets(x).Visible = -1 'reveal a sheet
> Sheets(x).Visible = 2 ' very hidden
>
> "FGOMEZ" wrote:
>
> > Every month, I update a workbook with 35 sheets that I have to break it
won
> > for 17 people, the sheets that I send varies for everyone (1 to 17
sheets),
> > the process I use is: create a copy of the sheet(s) as a new workbook,
what
> > I am looking for is to have the sheet(s) selected and run the macro to
do
> > the work (I would do it with a button).
> > I tries to use the macro recorder, but unfortunately comes with the
sheet
> > name.
> > Could somebody help me to get this task automated.
> >
> > Thanks
> >
> >
> >
> >

The idea is to set all of the sheets in the workbook to VeryHidden such that
they can only be unhidden with code. You would also have a sheet in the
workbook listing all of the people who will be getting the workbook (listed
by their logon name) and the sheet that they are allowed to view. At the
On_Open event the program checks the username of the person logged into the
computer and unhides the appropriate sheet. To do this correctly you need to
have the workbook protected. There are some security issues around doing
something like this and if a user is sophisticated enough they could break
into the other sheets (not likely but possible).
--
HTH...

Jim Thomlinson


"FGOMEZ" wrote:

> Roy,
> Thanks for your response, it sounds very interesting but this concept is
> totally new for me, when you say install a log on device for all users, what
> do you mean by that, how do I install that, also I understand how to hide
> sheets, but again not a clue of how to do it by user.
> Maybe you can explain a little bit more to me.
>
> Thanks
>
>
> "Roy" <Roy@discussions.microsoft.com> wrote in message
> news:18657501-B250-4D2A-BCC8-7E4D05AFDFFC@microsoft.com...
> > Let's say you install a log on device for all users. Based on who is
> logged
> > in, the workbook automatically reveals hidden sheets you grant them access
> to
> > via a hidden table. Everyone gets the same workbook. They log on and
> > automatically can only see what they are supposed to. You save yourself a
> lot
> > of work once you set it up initially. If it's just a matter of
> convenience,
> > they only need user names; if you need security as well, then they need
> > passwords too.
> >
> > Sheets(x).Visible = -1 'reveal a sheet
> > Sheets(x).Visible = 2 ' very hidden
> >
> > "FGOMEZ" wrote:
> >
> > > Every month, I update a workbook with 35 sheets that I have to break it
> won
> > > for 17 people, the sheets that I send varies for everyone (1 to 17
> sheets),
> > > the process I use is: create a copy of the sheet(s) as a new workbook,
> what
> > > I am looking for is to have the sheet(s) selected and run the macro to
> do
> > > the work (I would do it with a button).
> > > I tries to use the macro recorder, but unfortunately comes with the
> sheet
> > > name.
> > > Could somebody help me to get this task automated.
> > >
> > > Thanks
> > >
> > >
> > >
> > >
>
>
>

Don't forget to protect the project in the VBA editor.

> they can only be unhidden with code
You can change it manual in the VBA editor


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message news:0A122925-2EA4-44C5-A719-45651C6982CB@microsoft.com...
> The idea is to set all of the sheets in the workbook to VeryHidden such that
> they can only be unhidden with code. You would also have a sheet in the
> workbook listing all of the people who will be getting the workbook (listed
> by their logon name) and the sheet that they are allowed to view. At the
> On_Open event the program checks the username of the person logged into the
> computer and unhides the appropriate sheet. To do this correctly you need to
> have the workbook protected. There are some security issues around doing
> something like this and if a user is sophisticated enough they could break
> into the other sheets (not likely but possible).
> --
> HTH...
>
> Jim Thomlinson
>
>
> "FGOMEZ" wrote:
>
>> Roy,
>> Thanks for your response, it sounds very interesting but this concept is
>> totally new for me, when you say install a log on device for all users, what
>> do you mean by that, how do I install that, also I understand how to hide
>> sheets, but again not a clue of how to do it by user.
>> Maybe you can explain a little bit more to me.
>>
>> Thanks
>>
>>
>> "Roy" <Roy@discussions.microsoft.com> wrote in message
>> news:18657501-B250-4D2A-BCC8-7E4D05AFDFFC@microsoft.com...
>> > Let's say you install a log on device for all users. Based on who is
>> logged
>> > in, the workbook automatically reveals hidden sheets you grant them access
>> to
>> > via a hidden table. Everyone gets the same workbook. They log on and
>> > automatically can only see what they are supposed to. You save yourself a
>> lot
>> > of work once you set it up initially. If it's just a matter of
>> convenience,
>> > they only need user names; if you need security as well, then they need
>> > passwords too.
>> >
>> > Sheets(x).Visible = -1 'reveal a sheet
>> > Sheets(x).Visible = 2 ' very hidden
>> >
>> > "FGOMEZ" wrote:
>> >
>> > > Every month, I update a workbook with 35 sheets that I have to break it
>> won
>> > > for 17 people, the sheets that I send varies for everyone (1 to 17
>> sheets),
>> > > the process I use is: create a copy of the sheet(s) as a new workbook,
>> what
>> > > I am looking for is to have the sheet(s) selected and run the macro to
>> do
>> > > the work (I would do it with a button).
>> > > I tries to use the macro recorder, but unfortunately comes with the
>> sheet
>> > > name.
>> > > Could somebody help me to get this task automated.
>> > >
>> > > Thanks
>> > >
>> > >
>> > >
>> > >
>>
>>
>>

Good point. I make it a point to never send anyone anything that contains
something that they are not allowed to see. I assume the users all to be evil
geniuses who will find a way around the security and view that which they are
not supposed to see. I would go with removing the extra sheets. I can be a
little paranoid though.
--
HTH...

Jim Thomlinson


"Ron de Bruin" wrote:

> Don't forget to protect the project in the VBA editor.
>
> > they can only be unhidden with code
> You can change it manual in the VBA editor
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Jim Thomlinson" <jamest@tcgiRe-Move-This.com> wrote in message news:0A122925-2EA4-44C5-A719-45651C6982CB@microsoft.com...
> > The idea is to set all of the sheets in the workbook to VeryHidden such that
> > they can only be unhidden with code. You would also have a sheet in the
> > workbook listing all of the people who will be getting the workbook (listed
> > by their logon name) and the sheet that they are allowed to view. At the
> > On_Open event the program checks the username of the person logged into the
> > computer and unhides the appropriate sheet. To do this correctly you need to
> > have the workbook protected. There are some security issues around doing
> > something like this and if a user is sophisticated enough they could break
> > into the other sheets (not likely but possible).
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "FGOMEZ" wrote:
> >
> >> Roy,
> >> Thanks for your response, it sounds very interesting but this concept is
> >> totally new for me, when you say install a log on device for all users, what
> >> do you mean by that, how do I install that, also I understand how to hide
> >> sheets, but again not a clue of how to do it by user.
> >> Maybe you can explain a little bit more to me.
> >>
> >> Thanks
> >>
> >>
> >> "Roy" <Roy@discussions.microsoft.com> wrote in message
> >> news:18657501-B250-4D2A-BCC8-7E4D05AFDFFC@microsoft.com...
> >> > Let's say you install a log on device for all users. Based on who is
> >> logged
> >> > in, the workbook automatically reveals hidden sheets you grant them access
> >> to
> >> > via a hidden table. Everyone gets the same workbook. They log on and
> >> > automatically can only see what they are supposed to. You save yourself a
> >> lot
> >> > of work once you set it up initially. If it's just a matter of
> >> convenience,
> >> > they only need user names; if you need security as well, then they need
> >> > passwords too.
> >> >
> >> > Sheets(x).Visible = -1 'reveal a sheet
> >> > Sheets(x).Visible = 2 ' very hidden
> >> >
> >> > "FGOMEZ" wrote:
> >> >
> >> > > Every month, I update a workbook with 35 sheets that I have to break it
> >> won
> >> > > for 17 people, the sheets that I send varies for everyone (1 to 17
> >> sheets),
> >> > > the process I use is: create a copy of the sheet(s) as a new workbook,
> >> what
> >> > > I am looking for is to have the sheet(s) selected and run the macro to
> >> do
> >> > > the work (I would do it with a button).
> >> > > I tries to use the macro recorder, but unfortunately comes with the
> >> sheet
> >> > > name.
> >> > > Could somebody help me to get this task automated.
> >> > >
> >> > > Thanks
> >> > >
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>
>