Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet to worksheet . . .
I'm not looking for volumes of info on this one. (Um, that doesn't mean I
wouldn't appreciate it, though! LOL) I just need a jump start so I can figure out what to do to get started. I have a workbook with about 50 sheets. 4 of the sheets get data, dumped from a program. That data is carried to other sheets as needed. These "other" sheets basically just have a formula in each cell, so data is pulled from the main sheet it's "connected" to. Now that the workbook is working great, I FINALLY get feedback from our Engineering dept. They have 2 concerns . . . The dilemma . . . 1). If they send the finished workbook to the customer, the customer can view any/all formulae in the cells. They don't want the customer to see any formulae. 2). The size of the file is fairly large. Because not only are the final Engineering reports part of the workbook, but the sheets that do the "math", and our internal reports are all a part of the same workbook. So . . . they want to delete all but the 5 or 6 sheets with "their" data. But if they do . . . the source sheets supplying the data go, and their sheets end up with nothing in them. The solution(s) . . . I know I can hide formulae. Haven't yet gone in and tried to figure out how. But I know I can do that. So that part shouldn't be an issue. But . . .. They can't delete the source of the data! So what I'd like to do is . . . Make another workbook with just the Engineering portion. Then, once all the calculations are done, and the data is in the appropriate Engineering sheets, have the data go from the Engineering sheets in the main workbook . . . to the copy of the workbook. But just the values . . . not the formulae. As in, EX: Sheet D2 gets data from one program, does some calculations, and puts the results into a column in that sheet. The data from the colum with the calculated results is sent to sheet E2. How? Sheet E2 has a formula in each cell pulling that data over. You know . . . if something is in this cell on that sheet, put it here. Now comes workbook 2. I'd like sheet E2 of new workbook to receive the values of main E2. Without having a formula in every cell in the new E2. So when it's done, Engineering can send the new workbook to the customer, and still have the "working" workbook for reference. My best guess is . . . have all the cells in the heading section of new workbook use the formula to pull identical info (text) over, so the heading is done identical to main workbook. Use the "hide formula" function for the heading. Then somehow have all columns in main workbook sent over to new workbook, without forumlae in each cell of new workbook. (Ugghhh!) Does that make sense? :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet to worksheet . . .
There's 2 things you can do.
Separate Sheets to Reduce File Size: First, move the worksheets that you want the customer to be able to see to a new workbook, which you can do via the following steps: Select the sheets you want to move to a new workbook. Right click on one of those tabs below and click on "Move or Copy..." In the "To book:" click on "(new book)" Click on "OK" This will move those sheets to the new workbook. In the new workbook, you may have to redo your formulas to refer back to the old workbook. Hide formulas: To do this, it's a 2 step process Goto Format, Cells, Protection Checkmark both "Hidden" and "Lock" Click on OK Now, this will only take effect once you lock the worksheet and preferably done via a password, though it isn't foolproof, but it still a fairly good bet others won't unlock it as long as you use a good enough password, unless some sort of hashing program is used against it. To lock the worksheet, goto Tools, Protection, Protect Sheet... -- Ronald R. Dodge, Jr. Production Statistician/Programmer Master MOUS 2000 "Wayne Knazek" wrote in message ... I'm not looking for volumes of info on this one. (Um, that doesn't mean I wouldn't appreciate it, though! LOL) I just need a jump start so I can figure out what to do to get started. I have a workbook with about 50 sheets. 4 of the sheets get data, dumped from a program. That data is carried to other sheets as needed. These "other" sheets basically just have a formula in each cell, so data is pulled from the main sheet it's "connected" to. Now that the workbook is working great, I FINALLY get feedback from our Engineering dept. They have 2 concerns . . . The dilemma . . . 1). If they send the finished workbook to the customer, the customer can view any/all formulae in the cells. They don't want the customer to see any formulae. 2). The size of the file is fairly large. Because not only are the final Engineering reports part of the workbook, but the sheets that do the "math", and our internal reports are all a part of the same workbook. So . . . they want to delete all but the 5 or 6 sheets with "their" data. But if they do . . . the source sheets supplying the data go, and their sheets end up with nothing in them. The solution(s) . . . I know I can hide formulae. Haven't yet gone in and tried to figure out how. But I know I can do that. So that part shouldn't be an issue. But .. . . They can't delete the source of the data! So what I'd like to do is . . . Make another workbook with just the Engineering portion. Then, once all the calculations are done, and the data is in the appropriate Engineering sheets, have the data go from the Engineering sheets in the main workbook . . . to the copy of the workbook. But just the values . . . not the formulae. As in, EX: Sheet D2 gets data from one program, does some calculations, and puts the results into a column in that sheet. The data from the colum with the calculated results is sent to sheet E2. How? Sheet E2 has a formula in each cell pulling that data over. You know . . . if something is in this cell on that sheet, put it here. Now comes workbook 2. I'd like sheet E2 of new workbook to receive the values of main E2. Without having a formula in every cell in the new E2. So when it's done, Engineering can send the new workbook to the customer, and still have the "working" workbook for reference. My best guess is . . . have all the cells in the heading section of new workbook use the formula to pull identical info (text) over, so the heading is done identical to main workbook. Use the "hide formula" function for the heading. Then somehow have all columns in main workbook sent over to new workbook, without forumlae in each cell of new workbook. (Ugghhh!) Does that make sense? :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet to worksheet . . .
Here is a different approach which may help.
As I read what you say, you want several subsheets from the original finished workbook with no formulas for distribution. Here is the way you would do that. select the sheets you want (click the first, ctrl+Click additional). Do Edit=Move or Copy Sheet, click make a copy in the lower left. select new workbook and click OK. On each sheet in the new workbook Click at the juncture of the row and column headers so all cells are selected. Do edit=Copy, then immediatly to edit=Paste Special and select Values. This removes all formulas and just leaves the results they display. It removes links back to the original workbook which remains in tact. maybe that will at least help with part of the solution. -- Regards, Tom Ogilvy "Wayne Knazek" wrote in message ... I'm not looking for volumes of info on this one. (Um, that doesn't mean I wouldn't appreciate it, though! LOL) I just need a jump start so I can figure out what to do to get started. I have a workbook with about 50 sheets. 4 of the sheets get data, dumped from a program. That data is carried to other sheets as needed. These "other" sheets basically just have a formula in each cell, so data is pulled from the main sheet it's "connected" to. Now that the workbook is working great, I FINALLY get feedback from our Engineering dept. They have 2 concerns . . . The dilemma . . . 1). If they send the finished workbook to the customer, the customer can view any/all formulae in the cells. They don't want the customer to see any formulae. 2). The size of the file is fairly large. Because not only are the final Engineering reports part of the workbook, but the sheets that do the "math", and our internal reports are all a part of the same workbook. So . . . they want to delete all but the 5 or 6 sheets with "their" data. But if they do . . . the source sheets supplying the data go, and their sheets end up with nothing in them. The solution(s) . . . I know I can hide formulae. Haven't yet gone in and tried to figure out how. But I know I can do that. So that part shouldn't be an issue. But . . . They can't delete the source of the data! So what I'd like to do is . . . Make another workbook with just the Engineering portion. Then, once all the calculations are done, and the data is in the appropriate Engineering sheets, have the data go from the Engineering sheets in the main workbook . . . to the copy of the workbook. But just the values . . . not the formulae. As in, EX: Sheet D2 gets data from one program, does some calculations, and puts the results into a column in that sheet. The data from the colum with the calculated results is sent to sheet E2. How? Sheet E2 has a formula in each cell pulling that data over. You know . . . if something is in this cell on that sheet, put it here. Now comes workbook 2. I'd like sheet E2 of new workbook to receive the values of main E2. Without having a formula in every cell in the new E2. So when it's done, Engineering can send the new workbook to the customer, and still have the "working" workbook for reference. My best guess is . . . have all the cells in the heading section of new workbook use the formula to pull identical info (text) over, so the heading is done identical to main workbook. Use the "hide formula" function for the heading. Then somehow have all columns in main workbook sent over to new workbook, without forumlae in each cell of new workbook. (Ugghhh!) Does that make sense? :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet to worksheet . . .
You may consider writing or recording a macro that would, after you select
the sheets, copy them and do the paste special to a new file for you. -- JNW "Tom Ogilvy" wrote: Here is a different approach which may help. As I read what you say, you want several subsheets from the original finished workbook with no formulas for distribution. Here is the way you would do that. select the sheets you want (click the first, ctrl+Click additional). Do Edit=Move or Copy Sheet, click make a copy in the lower left. select new workbook and click OK. On each sheet in the new workbook Click at the juncture of the row and column headers so all cells are selected. Do edit=Copy, then immediatly to edit=Paste Special and select Values. This removes all formulas and just leaves the results they display. It removes links back to the original workbook which remains in tact. maybe that will at least help with part of the solution. -- Regards, Tom Ogilvy "Wayne Knazek" wrote in message ... I'm not looking for volumes of info on this one. (Um, that doesn't mean I wouldn't appreciate it, though! LOL) I just need a jump start so I can figure out what to do to get started. I have a workbook with about 50 sheets. 4 of the sheets get data, dumped from a program. That data is carried to other sheets as needed. These "other" sheets basically just have a formula in each cell, so data is pulled from the main sheet it's "connected" to. Now that the workbook is working great, I FINALLY get feedback from our Engineering dept. They have 2 concerns . . . The dilemma . . . 1). If they send the finished workbook to the customer, the customer can view any/all formulae in the cells. They don't want the customer to see any formulae. 2). The size of the file is fairly large. Because not only are the final Engineering reports part of the workbook, but the sheets that do the "math", and our internal reports are all a part of the same workbook. So . . . they want to delete all but the 5 or 6 sheets with "their" data. But if they do . . . the source sheets supplying the data go, and their sheets end up with nothing in them. The solution(s) . . . I know I can hide formulae. Haven't yet gone in and tried to figure out how. But I know I can do that. So that part shouldn't be an issue. But . . . They can't delete the source of the data! So what I'd like to do is . . . Make another workbook with just the Engineering portion. Then, once all the calculations are done, and the data is in the appropriate Engineering sheets, have the data go from the Engineering sheets in the main workbook . . . to the copy of the workbook. But just the values . . . not the formulae. As in, EX: Sheet D2 gets data from one program, does some calculations, and puts the results into a column in that sheet. The data from the colum with the calculated results is sent to sheet E2. How? Sheet E2 has a formula in each cell pulling that data over. You know . . . if something is in this cell on that sheet, put it here. Now comes workbook 2. I'd like sheet E2 of new workbook to receive the values of main E2. Without having a formula in every cell in the new E2. So when it's done, Engineering can send the new workbook to the customer, and still have the "working" workbook for reference. My best guess is . . . have all the cells in the heading section of new workbook use the formula to pull identical info (text) over, so the heading is done identical to main workbook. Use the "hide formula" function for the heading. Then somehow have all columns in main workbook sent over to new workbook, without forumlae in each cell of new workbook. (Ugghhh!) Does that make sense? :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet to worksheet . . .
Thaks, all! You gave me some ideas here.
Tom, I think you're closest to what I need to do. I need to automate this as much as is practical, as this process (data dump into master - move to Engineering workbook with no formulae) can be run many times a day. I'll play with this for a bit, then see what ?s I have. This is close. But I still need full automation. Maybe I can do it in VBA. "JNW" wrote: You may consider writing or recording a macro that would, after you select the sheets, copy them and do the paste special to a new file for you. -- JNW "Tom Ogilvy" wrote: Here is a different approach which may help. As I read what you say, you want several subsheets from the original finished workbook with no formulas for distribution. Here is the way you would do that. select the sheets you want (click the first, ctrl+Click additional). Do Edit=Move or Copy Sheet, click make a copy in the lower left. select new workbook and click OK. On each sheet in the new workbook Click at the juncture of the row and column headers so all cells are selected. Do edit=Copy, then immediatly to edit=Paste Special and select Values. This removes all formulas and just leaves the results they display. It removes links back to the original workbook which remains in tact. maybe that will at least help with part of the solution. -- Regards, Tom Ogilvy "Wayne Knazek" wrote in message ... I'm not looking for volumes of info on this one. (Um, that doesn't mean I wouldn't appreciate it, though! LOL) I just need a jump start so I can figure out what to do to get started. I have a workbook with about 50 sheets. 4 of the sheets get data, dumped from a program. That data is carried to other sheets as needed. These "other" sheets basically just have a formula in each cell, so data is pulled from the main sheet it's "connected" to. Now that the workbook is working great, I FINALLY get feedback from our Engineering dept. They have 2 concerns . . . The dilemma . . . 1). If they send the finished workbook to the customer, the customer can view any/all formulae in the cells. They don't want the customer to see any formulae. 2). The size of the file is fairly large. Because not only are the final Engineering reports part of the workbook, but the sheets that do the "math", and our internal reports are all a part of the same workbook. So . . . they want to delete all but the 5 or 6 sheets with "their" data. But if they do . . . the source sheets supplying the data go, and their sheets end up with nothing in them. The solution(s) . . . I know I can hide formulae. Haven't yet gone in and tried to figure out how. But I know I can do that. So that part shouldn't be an issue. But . . . They can't delete the source of the data! So what I'd like to do is . . . Make another workbook with just the Engineering portion. Then, once all the calculations are done, and the data is in the appropriate Engineering sheets, have the data go from the Engineering sheets in the main workbook . . . to the copy of the workbook. But just the values . . . not the formulae. As in, EX: Sheet D2 gets data from one program, does some calculations, and puts the results into a column in that sheet. The data from the colum with the calculated results is sent to sheet E2. How? Sheet E2 has a formula in each cell pulling that data over. You know . . . if something is in this cell on that sheet, put it here. Now comes workbook 2. I'd like sheet E2 of new workbook to receive the values of main E2. Without having a formula in every cell in the new E2. So when it's done, Engineering can send the new workbook to the customer, and still have the "working" workbook for reference. My best guess is . . . have all the cells in the heading section of new workbook use the formula to pull identical info (text) over, so the heading is done identical to main workbook. Use the "hide formula" function for the heading. Then somehow have all columns in main workbook sent over to new workbook, without forumlae in each cell of new workbook. (Ugghhh!) Does that make sense? :) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet to worksheet . . .
Recording a macro does give you VBA.
You can have your code in one workbook reserved just for code (maybe your personal.xls), and design it to work on the activeworkbook when you run it. -- Regards, Tom Ogilvy "Wayne Knazek" wrote: Thaks, all! You gave me some ideas here. Tom, I think you're closest to what I need to do. I need to automate this as much as is practical, as this process (data dump into master - move to Engineering workbook with no formulae) can be run many times a day. I'll play with this for a bit, then see what ?s I have. This is close. But I still need full automation. Maybe I can do it in VBA. "JNW" wrote: You may consider writing or recording a macro that would, after you select the sheets, copy them and do the paste special to a new file for you. -- JNW "Tom Ogilvy" wrote: Here is a different approach which may help. As I read what you say, you want several subsheets from the original finished workbook with no formulas for distribution. Here is the way you would do that. select the sheets you want (click the first, ctrl+Click additional). Do Edit=Move or Copy Sheet, click make a copy in the lower left. select new workbook and click OK. On each sheet in the new workbook Click at the juncture of the row and column headers so all cells are selected. Do edit=Copy, then immediatly to edit=Paste Special and select Values. This removes all formulas and just leaves the results they display. It removes links back to the original workbook which remains in tact. maybe that will at least help with part of the solution. -- Regards, Tom Ogilvy "Wayne Knazek" wrote in message ... I'm not looking for volumes of info on this one. (Um, that doesn't mean I wouldn't appreciate it, though! LOL) I just need a jump start so I can figure out what to do to get started. I have a workbook with about 50 sheets. 4 of the sheets get data, dumped from a program. That data is carried to other sheets as needed. These "other" sheets basically just have a formula in each cell, so data is pulled from the main sheet it's "connected" to. Now that the workbook is working great, I FINALLY get feedback from our Engineering dept. They have 2 concerns . . . The dilemma . . . 1). If they send the finished workbook to the customer, the customer can view any/all formulae in the cells. They don't want the customer to see any formulae. 2). The size of the file is fairly large. Because not only are the final Engineering reports part of the workbook, but the sheets that do the "math", and our internal reports are all a part of the same workbook. So . . . they want to delete all but the 5 or 6 sheets with "their" data. But if they do . . . the source sheets supplying the data go, and their sheets end up with nothing in them. The solution(s) . . . I know I can hide formulae. Haven't yet gone in and tried to figure out how. But I know I can do that. So that part shouldn't be an issue. But . . . They can't delete the source of the data! So what I'd like to do is . . . Make another workbook with just the Engineering portion. Then, once all the calculations are done, and the data is in the appropriate Engineering sheets, have the data go from the Engineering sheets in the main workbook . . . to the copy of the workbook. But just the values . . . not the formulae. As in, EX: Sheet D2 gets data from one program, does some calculations, and puts the results into a column in that sheet. The data from the colum with the calculated results is sent to sheet E2. How? Sheet E2 has a formula in each cell pulling that data over. You know . . . if something is in this cell on that sheet, put it here. Now comes workbook 2. I'd like sheet E2 of new workbook to receive the values of main E2. Without having a formula in every cell in the new E2. So when it's done, Engineering can send the new workbook to the customer, and still have the "working" workbook for reference. My best guess is . . . have all the cells in the heading section of new workbook use the formula to pull identical info (text) over, so the heading is done identical to main workbook. Use the "hide formula" function for the heading. Then somehow have all columns in main workbook sent over to new workbook, without forumlae in each cell of new workbook. (Ugghhh!) Does that make sense? :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet to worksheet . . .
I think this is pretty close to my solution. Will work on your suggestions.
Many thanks! "Tom Ogilvy" wrote: Recording a macro does give you VBA. You can have your code in one workbook reserved just for code (maybe your personal.xls), and design it to work on the activeworkbook when you run it. -- Regards, Tom Ogilvy "Wayne Knazek" wrote: Thaks, all! You gave me some ideas here. Tom, I think you're closest to what I need to do. I need to automate this as much as is practical, as this process (data dump into master - move to Engineering workbook with no formulae) can be run many times a day. I'll play with this for a bit, then see what ?s I have. This is close. But I still need full automation. Maybe I can do it in VBA. "JNW" wrote: You may consider writing or recording a macro that would, after you select the sheets, copy them and do the paste special to a new file for you. -- JNW "Tom Ogilvy" wrote: Here is a different approach which may help. As I read what you say, you want several subsheets from the original finished workbook with no formulas for distribution. Here is the way you would do that. select the sheets you want (click the first, ctrl+Click additional). Do Edit=Move or Copy Sheet, click make a copy in the lower left. select new workbook and click OK. On each sheet in the new workbook Click at the juncture of the row and column headers so all cells are selected. Do edit=Copy, then immediatly to edit=Paste Special and select Values. This removes all formulas and just leaves the results they display. It removes links back to the original workbook which remains in tact. maybe that will at least help with part of the solution. -- Regards, Tom Ogilvy "Wayne Knazek" wrote in message ... I'm not looking for volumes of info on this one. (Um, that doesn't mean I wouldn't appreciate it, though! LOL) I just need a jump start so I can figure out what to do to get started. I have a workbook with about 50 sheets. 4 of the sheets get data, dumped from a program. That data is carried to other sheets as needed. These "other" sheets basically just have a formula in each cell, so data is pulled from the main sheet it's "connected" to. Now that the workbook is working great, I FINALLY get feedback from our Engineering dept. They have 2 concerns . . . The dilemma . . . 1). If they send the finished workbook to the customer, the customer can view any/all formulae in the cells. They don't want the customer to see any formulae. 2). The size of the file is fairly large. Because not only are the final Engineering reports part of the workbook, but the sheets that do the "math", and our internal reports are all a part of the same workbook. So . . . they want to delete all but the 5 or 6 sheets with "their" data. But if they do . . . the source sheets supplying the data go, and their sheets end up with nothing in them. The solution(s) . . . I know I can hide formulae. Haven't yet gone in and tried to figure out how. But I know I can do that. So that part shouldn't be an issue. But . . . They can't delete the source of the data! So what I'd like to do is . . . Make another workbook with just the Engineering portion. Then, once all the calculations are done, and the data is in the appropriate Engineering sheets, have the data go from the Engineering sheets in the main workbook . . . to the copy of the workbook. But just the values . . . not the formulae. As in, EX: Sheet D2 gets data from one program, does some calculations, and puts the results into a column in that sheet. The data from the colum with the calculated results is sent to sheet E2. How? Sheet E2 has a formula in each cell pulling that data over. You know . . . if something is in this cell on that sheet, put it here. Now comes workbook 2. I'd like sheet E2 of new workbook to receive the values of main E2. Without having a formula in every cell in the new E2. So when it's done, Engineering can send the new workbook to the customer, and still have the "working" workbook for reference. My best guess is . . . have all the cells in the heading section of new workbook use the formula to pull identical info (text) over, so the heading is done identical to main workbook. Use the "hide formula" function for the heading. Then somehow have all columns in main workbook sent over to new workbook, without forumlae in each cell of new workbook. (Ugghhh!) Does that make sense? :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Discussion (Misc queries) | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Programming | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |