Setting the Print Area in a macro.

[Dick Snow]

I'm new to VBA and have a problem. Each month I create a cvs file for
monthy warranty claims. I open this with Excel. Then I create 13
different reports using Autofilter. Everthing I've done works like a
charm except setting the print area. No matter how I arrive at the print
range, the macro uses the print area ranges (see next 3 lines)
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$55"
This works fine for this month but next month all the reports will be
different sizes. The fact that the macro defines the "Print Area" makes
it useless for the following months.
Is there any way to get around this?
Thanks in advance

 

[Jim Cone]

Dick,
Maybe this is what you are trying to do?...

ActiveSheet.PageSetup.PrintArea = Selection.Address

Jim Cone
San Francisco, USA



I'm new to VBA and have a problem. Each month I create a cvs file for
monthy warranty claims. I open this with Excel. Then I create 13
different reports using Autofilter. Everthing I've done works like a
charm except setting the print area. No matter how I arrive at the print
range, the macro uses the print area ranges (see next 3 lines)
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$55"
This works fine for this month but next month all the reports will be
different sizes. The fact that the macro defines the "Print Area" makes
it useless for the following months.
Is there any way to get around this?
Thanks in advance

 

[Dick Snow]

Jim Cone,
What a timely and most welcome reply. Your answer corrected my
problem. I have 4 books that I'm learning from and none gave me the
information I needed. You're greatly appreaciated.
Thank you.