Below is the code I am currently using to set the print range, header title,
and put a border around the selected print area. I need some help revising
the code to allow for a variable range. The beginning cell will always be
A1 (1, 1), but the lower left cell that defines the boundary of the print
area will change depending on data that is imported into the worksheet.
Column B will determine the row that will defines the last cell. It should
be two rows below the Cell in Column B that Contains the text "Target %
Comp." The same row will determine will determine the column for the last
cell. It will be one column over from the last cell that contains data on
the row where "Target % Comp." is located. All other cells on this row will
be blank.

For example: Now "Target % Comp." is in Row "41" and the last data is in
Column "AS", by going 2 rows down and one row to the left the last cell
should be AT43 as listed in the code below.


Sub SetPrintRange()
With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = "$A$1:$AT$43"
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
Range("$A$1:AT43").BorderAround Weight:=xlThin
End With
End Sub

I assume you meant "2 rows down and 1 column to the right":

Public Function MyPrintArea(SheetName As String) As Range

Dim FoundIt As Range

Set FoundIt = Sheets(SheetName).Range("B:B").Find("Target % Comp.")

If Not (FoundIt Is Nothing) Then

Set MyPrintArea = FoundIt.End(xlToRight)
Set MyPrintArea = MyPrintArea.Offset(2, 1).Range("A1")
Set MyPrintArea = Sheets(SheetName).Range("A1:" & MyPrintArea.Address)

End If

End Function

Sub SetPrintRange()

Dim PrintRange as Range
Set PrintRange = MyPrintArea("Charts")

With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = PrintRange
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
PrintRange.BorderAround Weight:=xlThin
End With
End Sub



"Joel Mills" wrote:

Below is the code I am currently using to set the print range, header title,
and put a border around the selected print area. I need some help revising
the code to allow for a variable range. The beginning cell will always be
A1 (1, 1), but the lower left cell that defines the boundary of the print
area will change depending on data that is imported into the worksheet.
Column B will determine the row that will defines the last cell. It should
be two rows below the Cell in Column B that Contains the text "Target %
Comp." The same row will determine will determine the column for the last
cell. It will be one column over from the last cell that contains data on
the row where "Target % Comp." is located. All other cells on this row will
be blank.

For example: Now "Target % Comp." is in Row "41" and the last data is in
Column "AS", by going 2 rows down and one row to the left the last cell
should be AT43 as listed in the code below.


Sub SetPrintRange()
With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = "$A$1:$AT$43"
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
Range("$A$1:AT43").BorderAround Weight:=xlThin
End With
End Sub

Yes you are right. I've tried pasting the following code into a module.
Alt+F11, but I get a runtime error '5' invalid procedure call or argument.
When I click on debug ".PrintArea = PrintRange" is highlighted in yellow.
I'm not sure what I'm doing wrong.


"K Dales" wrote in message
...
I assume you meant "2 rows down and 1 column to the right":

Public Function MyPrintArea(SheetName As String) As Range

Dim FoundIt As Range

Set FoundIt = Sheets(SheetName).Range("B:B").Find("Target % Comp.")

If Not (FoundIt Is Nothing) Then

Set MyPrintArea = FoundIt.End(xlToRight)
Set MyPrintArea = MyPrintArea.Offset(2, 1).Range("A1")
Set MyPrintArea = Sheets(SheetName).Range("A1:" & MyPrintArea.Address)

End If

End Function

Sub SetPrintRange()

Dim PrintRange as Range
Set PrintRange = MyPrintArea("Charts")

With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = PrintRange
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
PrintRange.BorderAround Weight:=xlThin
End With
End Sub



"Joel Mills" wrote:

Below is the code I am currently using to set the print range, header
title,
and put a border around the selected print area. I need some help
revising
the code to allow for a variable range. The beginning cell will always
be
A1 (1, 1), but the lower left cell that defines the boundary of the
print
area will change depending on data that is imported into the worksheet.
Column B will determine the row that will defines the last cell. It
should
be two rows below the Cell in Column B that Contains the text "Target %
Comp." The same row will determine will determine the column for the
last
cell. It will be one column over from the last cell that contains data
on
the row where "Target % Comp." is located. All other cells on this row
will
be blank.

For example: Now "Target % Comp." is in Row "41" and the last data is
in
Column "AS", by going 2 rows down and one row to the left the last cell
should be AT43 as listed in the code below.


Sub SetPrintRange()
With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = "$A$1:$AT$43"
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
Range("$A$1:AT43").BorderAround Weight:=xlThin
End With
End Sub

change
.PrintArea = PrintRange

to

.PrintArea = PrintRange.Address(External:=True)

PrintArea is looking for a string.

--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
Yes you are right. I've tried pasting the following code into a module.
Alt+F11, but I get a runtime error '5' invalid procedure call or argument.
When I click on debug ".PrintArea = PrintRange" is highlighted in yellow.
I'm not sure what I'm doing wrong.


"K Dales" wrote in message
...
I assume you meant "2 rows down and 1 column to the right":

Public Function MyPrintArea(SheetName As String) As Range

Dim FoundIt As Range

Set FoundIt = Sheets(SheetName).Range("B:B").Find("Target % Comp.")

If Not (FoundIt Is Nothing) Then

Set MyPrintArea = FoundIt.End(xlToRight)
Set MyPrintArea = MyPrintArea.Offset(2, 1).Range("A1")
Set MyPrintArea = Sheets(SheetName).Range("A1:" &
MyPrintArea.Address)

End If

End Function

Sub SetPrintRange()

Dim PrintRange as Range
Set PrintRange = MyPrintArea("Charts")

With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = PrintRange
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
PrintRange.BorderAround Weight:=xlThin
End With
End Sub



"Joel Mills" wrote:

Below is the code I am currently using to set the print range, header
title,
and put a border around the selected print area. I need some help
revising
the code to allow for a variable range. The beginning cell will
always
be
A1 (1, 1), but the lower left cell that defines the boundary of the
print
area will change depending on data that is imported into the
worksheet.
Column B will determine the row that will defines the last cell. It
should
be two rows below the Cell in Column B that Contains the text "Target
%
Comp." The same row will determine will determine the column for the
last
cell. It will be one column over from the last cell that contains
data
on
the row where "Target % Comp." is located. All other cells on this
row
will
be blank.

For example: Now "Target % Comp." is in Row "41" and the last data is
in
Column "AS", by going 2 rows down and one row to the left the last
cell
should be AT43 as listed in the code below.


Sub SetPrintRange()
With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = "$A$1:$AT$43"
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
Range("$A$1:AT43").BorderAround Weight:=xlThin
End With
End Sub

K.Dales & Tom, thanks for the help. This works now.

"Tom Ogilvy" wrote in message
...
change
.PrintArea = PrintRange

to

.PrintArea = PrintRange.Address(External:=True)

PrintArea is looking for a string.

--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
Yes you are right. I've tried pasting the following code into a module.
Alt+F11, but I get a runtime error '5' invalid procedure call or
argument.
When I click on debug ".PrintArea = PrintRange" is highlighted in
yellow.
I'm not sure what I'm doing wrong.


"K Dales" wrote in message
...
I assume you meant "2 rows down and 1 column to the right":

Public Function MyPrintArea(SheetName As String) As Range

Dim FoundIt As Range

Set FoundIt = Sheets(SheetName).Range("B:B").Find("Target % Comp.")

If Not (FoundIt Is Nothing) Then

Set MyPrintArea = FoundIt.End(xlToRight)
Set MyPrintArea = MyPrintArea.Offset(2, 1).Range("A1")
Set MyPrintArea = Sheets(SheetName).Range("A1:" &
MyPrintArea.Address)

End If

End Function

Sub SetPrintRange()

Dim PrintRange as Range
Set PrintRange = MyPrintArea("Charts")

With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = PrintRange
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
PrintRange.BorderAround Weight:=xlThin
End With
End Sub



"Joel Mills" wrote:

Below is the code I am currently using to set the print range,
header
title,
and put a border around the selected print area. I need some help
revising
the code to allow for a variable range. The beginning cell will
always
be
A1 (1, 1), but the lower left cell that defines the boundary of the
print
area will change depending on data that is imported into the
worksheet.
Column B will determine the row that will defines the last cell. It
should
be two rows below the Cell in Column B that Contains the text
"Target
%
Comp." The same row will determine will determine the column for
the
last
cell. It will be one column over from the last cell that contains
data
on
the row where "Target % Comp." is located. All other cells on this
row
will
be blank.

For example: Now "Target % Comp." is in Row "41" and the last data
is
in
Column "AS", by going 2 rows down and one row to the left the last
cell
should be AT43 as listed in the code below.


Sub SetPrintRange()
With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = "$A$1:$AT$43"
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
Range("$A$1:AT43").BorderAround Weight:=xlThin
End With
End Sub