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?

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

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.