Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a values-only copy - page breaks a problem
I create a lot of financial models that I send to potential clients.
In the early stages of negotiation, I tend not to share the working model but send a copy with all the formulae replaced by their values - a snapshot, if you like. I find that Excel models tend to grow into rather large files through the process of making changes such that if one performs a copy and paste-special-values in the workbook that one has been using for the working model, the file size of the resulting model tends to be much larger than it would have been if the same values and formatting had been created from scratch. Therefore, I have a macro that creates a new workbook and copies the values and formatting and page setup properties etc. from the working model. This also allows me to use grouping levels to hide rows or columns that are simply used for interim calculations in the working model so that they do not appear in the new workbook. The one remaining annoyance for me is the difficulty of copying manual page breaks when, because of unwanted hidden columns in the working model, the location of the page breaks in the destination workbook needs to be different in cell-reference terms, i.e. a page break that occurs at location X1 in the source model might need to be at P1 in the destination model. I've never worked with page breaks in VBA before so can anyone suggest an approach to solving this problem? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a values-only copy - page breaks a problem
After you've created that values only worksheet/workbook, record a macro that
removes the pagebreaks. Then reapply the page breaks where you want. If you have some rules to follow, you could include them in your code, too. janderson wrote: I create a lot of financial models that I send to potential clients. In the early stages of negotiation, I tend not to share the working model but send a copy with all the formulae replaced by their values - a snapshot, if you like. I find that Excel models tend to grow into rather large files through the process of making changes such that if one performs a copy and paste-special-values in the workbook that one has been using for the working model, the file size of the resulting model tends to be much larger than it would have been if the same values and formatting had been created from scratch. Therefore, I have a macro that creates a new workbook and copies the values and formatting and page setup properties etc. from the working model. This also allows me to use grouping levels to hide rows or columns that are simply used for interim calculations in the working model so that they do not appear in the new workbook. The one remaining annoyance for me is the difficulty of copying manual page breaks when, because of unwanted hidden columns in the working model, the location of the page breaks in the destination workbook needs to be different in cell-reference terms, i.e. a page break that occurs at location X1 in the source model might need to be at P1 in the destination model. I've never worked with page breaks in VBA before so can anyone suggest an approach to solving this problem? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a values-only copy - page breaks a problem
janderson wrote: Therefore, I have a macro that creates a new workbook and copies the values and formatting and page setup properties etc. from the working model. This also allows me to use grouping levels to hide rows or columns that are simply used for interim calculations in the working model so that they do not appear in the new workbook. The one remaining annoyance for me is the difficulty of copying manual page breaks when, because of unwanted hidden columns in the working model, the location of the page breaks in the destination workbook needs to be different in cell-reference terms, i.e. a page break that occurs at location X1 in the source model might need to be at P1 in the destination model. I've never worked with page breaks in VBA before so can anyone suggest an approach to solving this problem? Dave Peterson wrote: After you've created that values only worksheet/workbook, record a macro that removes the pagebreaks. Then reapply the page breaks where you want. If you have some rules to follow, you could include them in your code, too. Dave Peterson Thanks for the suggestion. The thing is, "where I want" is where they were in the source workbook. What I have now done is to copy even the hidden rows and columns into the destination workbook. Then I apply page setup properties and also the pagebreaks. Then I delete the rows and columns that were hidden in the source workbook. If done this way round, there is no need to keep track of page break locations. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
page breaks problem - help !! | Excel Discussion (Misc queries) | |||
Copy Rows After Page Breaks | Excel Discussion (Misc queries) | |||
Page Breaks Problem ( Please Help) | Excel Programming | |||
([([([ Page Breaks Problem ])])]) | Excel Programming | |||
Page breaks in Sheet for copy via OLE? | Excel Programming |