Macro print range not dynamic like I wanted it to be

[TRYING]

Sub DataPrint()
'
' DataPrint Macro
'
' Keyboard Shortcut: Ctrl+d
'
Application.Goto Reference:="DataTopLeft"
Selection.CurrentRegion.Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$E$209"
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Above is a macro I recorded. "DataTopLeft" is cell A1 which contains a
non-printing apostrophe. When recording the macro I started by going to
DataTopLeft. Then I clicked on Select Current Region icon which defined the
region as "$A$1:$E$209". Then I clicked the Set Print Area icon. Then I
clicked on the Print Preview icon. I then clicked the Stop Recording icon.

The print area for next month could be A1 to E215 or A1 to E198 or some
other area.

How can I make the macro do what I want and what I thought I did when I
recorded it, which is select the current range and set that as the print
range?

Thanks.

 

[Dave Peterson]

Sub DataPrint()
'
' DataPrint Macro
'
' Keyboard Shortcut: Ctrl+d
'
Application.Goto Reference:="DataTopLeft"
Selection.CurrentRegion.Select
ActiveSheet.PageSetup.PrintArea = Selection.address
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Or without changing the printarea:

activesheet.range("a1").currentregion.printpreview

 

[Gord Dibben]

Sub DataPrint()
'
' DataPrint Macro
'
' Keyboard Shortcut: Ctrl+d
'
Application.Goto Reference:="DataTopLeft"
ActiveSheet.PageSetup.PrintArea = CurrentRegion
ActiveWindow.SelectedSheets.PrintPreview
End Sub


Gord Dibben MS Excel MVP

 

[TRYING]

Thanks, Dave. I tried your first solution. The macro now works like I want it
to.

Follow-up question: Since I dont' know enough to write macros myself, I rely
on macro recording. Why did my recorded macro fix the print range at
$A$1:$E$209 instead of selecting the current region like it wanted it to do
when I clicked on the Select Current Region icon during macro recording?

 

[TRYING]

Thanks Gord. I also tried your solution and it works as far as print range
selection is concerned, but it also altered the print setup in other ways
that didn't suit my purpose.

 

[Dave Peterson]

That's the way recording macros works--it records the actions you take against
the ranges you select.

 

[Gord Dibben]

Now I'm curious.

In what other ways was the print setup altered?


Gord

 

[TRYING]

For one, the print range extended beyond column E, something like column O or
P (I can't tell because the Print Preview only gives me two views--very small
and even smaller). Column F is completely empty from row 1 to row 65,000+. I
designed it this way so that when I click on Select Current Range, the range
will stop at column E. Other columns to the right of column F have formulas
and text but I don't want these columns in the print range.

 

[TRYING]

I guess I'll just record first and then fix later with the help of you
gracious people. Thanks.