Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy sheet(s)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy sheet(s)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy sheet(s)
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy sheet(s)
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy sheet(s)
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" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy sheet(s)
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro - copy/add to another sheet | Excel Discussion (Misc queries) | |||
Macro to copy a sheet and name it | Excel Discussion (Misc queries) | |||
Copy Macro values to new sheet | New Users to Excel | |||
Macro to copy sheet | Excel Programming | |||
How to create a Macro to Copy Information in one sheet to another sheet. | Excel Programming |