Printing named range only

[Guest]

Is it possible to "automate" print selection only when selecting a named range
I have a range called Boss_Print that is specific to what she wants to see from the report. But, each time I select the range, "Boss_Print" range, I have to select "pritn selection only"

Can this be programmed via a macro?

AND, while I have you; how can I make this range dynamic? Each week, cells are added to the bottom of the range (expanding the range; so my workaround was to name the range with far more cells (rows only) than I need at present) and rows are hidden for those items that have been completed

Looking forward to your help....as always! With thanks in advance!

 

[Don Guillett]

insert>name>define>name it prnrng>in the refers to type in
=offset($a$1,0,0,counta($A:$A),3)
Now the prnrng will be self adjusting based on whats in col A. If you have
bo1 bo1 1
bo2
bo3 bo3 3
bo4 bo4 4
x

then A1:C5 will be printed.
Now goto the FILE menu>print preview>setup>printarea>type in prnrng>enter.
TEST.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Is it possible to "automate" print selection only when selecting a named range?
I have a range called Boss_Print that is specific to what she wants to see

from the report. But, each time I select the range, "Boss_Print" range, I
have to select "pritn selection only".

Can this be programmed via a macro?

AND, while I have you; how can I make this range dynamic? Each week,

cells are added to the bottom of the range (expanding the range; so my
workaround was to name the range with far more cells (rows only) than I need
at present) and rows are hidden for those items that have been completed.

 

[ray]

One way
Add the "Set Print Area" icon to a tool bar.
If you insert rows in the middle of the range it will
expand itself.
good luck

-----Original Message-----
Is it possible to "automate" print selection only when selecting a named range?
I have a range called Boss_Print that is specific to what

she wants to see from the report. But, each time I select
the range, "Boss_Print" range, I have to select "pritn
selection only".

Can this be programmed via a macro?

AND, while I have you; how can I make this range

dynamic? Each week, cells are added to the bottom of the
range (expanding the range; so my workaround was to name
the range with far more cells (rows only) than I need at
present) and rows are hidden for those items that have
been completed.

 

[Guest]

Thanks, Don...but I still have a question

The s/s is actually Sheet 1
A1 = updated DATE =TODAY()
A2 = header ro
A3:A9 = advanced filter criteri
A10 = header row (repeated
A11:O210 - current range (as of today
BUT, tomorrow that will increase by rows only

HOWEVER, the range for BOSS_PRINT is as follows
A10:N210 (current range as of today
Again, this will increase by ROWS only

And, this will be the selection I want to print (A10:N??) when I select BOSS_PRINT (dynamic named range)
Does your answer still apply

And, can I apply your same formula to the ORIGINAL range (A10:O??) in much the same way

Thanks again for your kind assistance.

 

[Guest]

I tested this and it worked like a charm

Here's the final part to the question, Don-

Does this mean I have to manually change the print area each time I want to print either BOSS_PRINT range and the entire range for myself

Is there a way to automate the selection to toggle between the two options?

 

[Don Guillett]

You would need to design a macro to choose the prnrng or another range or do
it manually as Ray suggested.

Here is one way using the prnrng you created.

Sub ChoosePrintRange()
ans = InputBox("print all =1 or prnrng=2")
If ans = 1 Then
ActiveSheet.PageSetup.PrintArea = "$D$1:$G$6"
ActiveWindow.SelectedSheets.PrintPreview
Else
ActiveSheet.PageSetup.PrintArea = prnrng
ActiveWindow.SelectedSheets.PrintPreview
End If
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

I tested this and it worked like a charm!

Here's the final part to the question, Don--

Does this mean I have to manually change the print area each time I want

to print either BOSS_PRINT range and the entire range for myself?

Is there a way to automate the selection to toggle between the two

options?

 

[Guest]

Ok, now I'm really puzzled..

I used the offset function you provided and changed the last digit from 3 to 14 to allow for all of the needed columns
BUT, for some reason (I have tried EVERYTHING and can't find what the problem is), the result only displays rows A1 thru N172

Rows 173:204 are hidden (shift>alt>right arrow) but the list continues again @ row 205 thru 210. But, I can't seem to expand the result to include these last few rows. Your help is most appreciated. I am pulling my hair out over this one and they all look grey now!

I don't think that it could be the hidden rows stopping it becuase there are a number of instances of the same and it didn't affect the printout. (such as rows 103:120 and 122:148 are also hidden (grouped, if you will) in the same way as 173:204

Any ideas what I am doing wrong here?

 

[Don Guillett]

I don't quite understand. Hidden rows are not printed. The method I gave
should work. Feel free to send me a SMALL workbook to have a look.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Ok, now I'm really puzzled...

I used the offset function you provided and changed the last digit from 3

to 14 to allow for all of the needed columns.

BUT, for some reason (I have tried EVERYTHING and can't find what the

problem is), the result only displays rows A1 thru N172.

Rows 173:204 are hidden (shift>alt>right arrow) but the list continues

again @ row 205 thru 210. But, I can't seem to expand the result to include
these last few rows. Your help is most appreciated. I am pulling my hair
out over this one and they all look grey now!!

I don't think that it could be the hidden rows stopping it becuase there

are a number of instances of the same and it didn't affect the printout.
(such as rows 103:120 and 122:148 are also hidden (grouped, if you will) in
the same way as 173:204.

 

[Guest]

thanks for the offer of help, Don. I have sent you the file from my home address. Feel free to reply directly

Thanks again SO much!

 

[Guest]

I just had to write a follow-up to say thank you to Don G. for his kind and patient assistance this past weekend
I was able to add the macro this morning and now I can print EITHER way (all or part) based on his helpful offset formulas

Thanks to the newsgroup in general and Don in particular!

Best regards
marcy

 

[Don Guillett]

thanks for the thanks and glad to help

--
Don Guillett
SalesAid Software
(e-mail address removed)

I just had to write a follow-up to say thank you to Don G. for his kind

and patient assistance this past weekend.

I was able to add the macro this morning and now I can print EITHER way

(all or part) based on his helpful offset formulas.