Printing with macros

[Dean]

Though there is some overlap with a prior post (which is finished), I think it best to start anew.

I have many worksheets (say 20) each of variable width (versus one another as well as when rerun monthly), say from 10 to 120 columns wide. They are all only page high, fortunately. One of the key readers is an 87 yr old chairman and he requires a bigger font, say 1/4 inch wide for 7,756 on a printed hard copy. Also, I would want all page breaks to be only after a totals columns, which would require counting up non-hidden columns (I have a macro that hides these unused columns).

Given headers and footers and all, it is proving to be a nasty manual exercise (varying font sizes, margins for headers, # of pages of width, to satisfy this requirement, seemingly involving trial and error. I know that I am not up to the task of programming VBA to do all this. My question is how hard it would be for an expert like one of you guys to do this. The next question would be if you know someone who would do it!

Any thoughts would be appreciated. Excuse me if my definition is inadequate.

Thanks!
Dean

 

[Guest]

Not too sure why this is over your head. Make a copy of the workbook. Start
recording a macro and then do all of the necessary formatting. Stop
recording. Record a second macro and format it all back again... Run the two
macros to ensure that they work. If anything does not work out let us know
and we can help you through it...

Genarally speaking we are not here to do the driving. If you end up in the
ditch or can't quite figure out the map we are only to happy to get you on
the road and pointed in the right direction.

 

[Dean]

I truly appreciate the intent and spirit of your response, but perhaps I did
not explain my predicament well enough. Every month the file is 're-run',
and the # of columns change on each and every of the 20 worksheets -
typically 75% of potential columns are hidden because they turn up empty,
etc, etc. In order to get the printed (on a piece of paper) font sizes big
enough, I have to play with margins, including header and footer, the # of
pages of width, and sometimes even underlying font sizes - I then need to
make sure that each page break is just to the right of a Totals column,
though not at every totals column. Right now, it is totally trial and
error, because there is some algorithm of proportionality/sizing that EXCEL
uses and I don't know its algorithm. I've noticed that, when you want
bigger printed images, it is harder and harder to deal with this (highly
leveraged) - in one case, I had to use fonts of about 72, it seemed!!

Though I greatly appreciate your offer of free help, and I've used it more
often than I'm entitled to, if you check the # of posts and replies between
me and (mostly) Dave Peterson on a thread I started on 9/7 at 10:18 PM
called "print rows", as an example, you will know I'm not up to this task.
I'm not even sure it's really do-able in some automated algorithmic fashion,
unless one knows how EXCEL sizes things, amongst header, footers, exhibit in
between etc, etc. I was trying to ask for opinions on that.

Perhaps I should not be asking to pay for help from folks here, but that was
what I was suggesting, not free help. It would not be fair, given my level
of expertise to ask you folks to suffer through it.

I hope this helps explain the situation better.

Thanks!
Dean

 

[Guest]

Here are some links to look at for consulting. There are lots more. Look for
the MVP designation...

http://www.mcgimpsey.com/ma/consulting.html
http://www.cpearson.com/excel.htm
http://www.contextures.com/

 

[Dean]

OK, thanks much. These names seem to be mentioned often, so I will be
comfortable with them, if I need them.

Actually, for now, what I have done is created a few macros that, for the
block of columns or rows, change height, change width (with a hide empty
columns integrated into it, since it seems to undo the hidden columns), and
change font size, followed by a print preview. This allows the user some
easy trial and error - maybe that will suffice for now.

Thanks again!
Dean