Macro print range not dynamic like I wanted it to be
 

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.

Macro print range not dynamic like I wanted it to be
 

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



TRYING wrote:

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

Macro print range not dynamic like I wanted it to be
 

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

On Mon, 26 Nov 2007 13:21:00 -0800, TRYING
wrote:

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.

Macro print range not dynamic like I wanted it to be
 

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?

"Dave Peterson" wrote:

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



TRYING wrote:

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

Macro print range not dynamic like I wanted it to be
 

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.

"Gord Dibben" wrote:

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

On Mon, 26 Nov 2007 13:21:00 -0800, TRYING
wrote:

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.

Macro print range not dynamic like I wanted it to be
 

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


TRYING wrote:

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?

"Dave Peterson" wrote:

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



TRYING wrote:

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


--

Dave Peterson

Macro print range not dynamic like I wanted it to be
 

Now I'm curious.

In what other ways was the print setup altered?


Gord

On Mon, 26 Nov 2007 15:00:00 -0800, TRYING
wrote:

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.

"Gord Dibben" wrote:

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

On Mon, 26 Nov 2007 13:21:00 -0800, TRYING
wrote:

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.

Macro print range not dynamic like I wanted it to be
 

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.



"Gord Dibben" wrote:

Now I'm curious.

In what other ways was the print setup altered?


Gord

On Mon, 26 Nov 2007 15:00:00 -0800, TRYING
wrote:

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.

"Gord Dibben" wrote:

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

On Mon, 26 Nov 2007 13:21:00 -0800, TRYING
wrote:

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.

Macro print range not dynamic like I wanted it to be
 

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

"Dave Peterson" wrote:

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


TRYING wrote:

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?

"Dave Peterson" wrote:

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



TRYING wrote:

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


--

Dave Peterson