How to write a macro to select print ranges that vary monthly
 

I have multiple spreadsheets with varying print ranges that change monthly.
I'm trying to figure out the macro using sendkeys, but not working very well.
Any suggestions?

How to write a macro to select print ranges that vary monthly
 

What determines the extent of the print range?

does the upper left corner never change and can we assume the print ranges
are contiguous blocks of data separated from other data by the edge of the
sheet or blank rows and columns. If so

Worksheets("Sheet2").Select
Range("B9").CurrentRegion.Select


--
Regards,
Tom Ogilvy

"wilson@irco" wrote in message
...
I have multiple spreadsheets with varying print ranges that change
monthly.
I'm trying to figure out the macro using sendkeys, but not working very
well.
Any suggestions?

How to write a macro to select print ranges that vary monthly
 

Thank you Tom. Unfortunately not that simple. The data does always start in
A1, but due to the condition that additional lines are added next mo.
formulas extend beyond this month's print range. There are also column
breaks (H,L and Q). The end column remains constant (S).

"Tom Ogilvy" wrote:

What determines the extent of the print range?

does the upper left corner never change and can we assume the print ranges
are contiguous blocks of data separated from other data by the edge of the
sheet or blank rows and columns. If so

Worksheets("Sheet2").Select
Range("B9").CurrentRegion.Select


--
Regards,
Tom Ogilvy

"wilson@irco" wrote in message
...
I have multiple spreadsheets with varying print ranges that change
monthly.
I'm trying to figure out the macro using sendkeys, but not working very
well.
Any suggestions?

How to write a macro to select print ranges that vary monthly
 

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

Assuming that column A does not contain formulas but contains Data.

--
Regards,
Tom Ogilvy


"wilson@irco" wrote in message
...
Thank you Tom. Unfortunately not that simple. The data does always start
in
A1, but due to the condition that additional lines are added next mo.
formulas extend beyond this month's print range. There are also column
breaks (H,L and Q). The end column remains constant (S).

"Tom Ogilvy" wrote:

What determines the extent of the print range?

does the upper left corner never change and can we assume the print
ranges
are contiguous blocks of data separated from other data by the edge of
the
sheet or blank rows and columns. If so

Worksheets("Sheet2").Select
Range("B9").CurrentRegion.Select


--
Regards,
Tom Ogilvy

"wilson@irco" wrote in message
...
I have multiple spreadsheets with varying print ranges that change
monthly.
I'm trying to figure out the macro using sendkeys, but not working
very
well.
Any suggestions?

How to write a macro to select print ranges that vary monthly
 

Tom, I'm pretty much an amatuer here and am not sure where I identify,insert
the various sheet names within the code you sent. Do I have to put anything
in the brackets on line one of your message? Any other lines I need to
adjust to customize for my application? Thanks.

"Tom Ogilvy" wrote:

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

Assuming that column A does not contain formulas but contains Data.

--
Regards,
Tom Ogilvy


"wilson@irco" wrote in message
...
Thank you Tom. Unfortunately not that simple. The data does always start
in
A1, but due to the condition that additional lines are added next mo.
formulas extend beyond this month's print range. There are also column
breaks (H,L and Q). The end column remains constant (S).

"Tom Ogilvy" wrote:

What determines the extent of the print range?

does the upper left corner never change and can we assume the print
ranges
are contiguous blocks of data separated from other data by the edge of
the
sheet or blank rows and columns. If so

Worksheets("Sheet2").Select
Range("B9").CurrentRegion.Select


--
Regards,
Tom Ogilvy

"wilson@irco" wrote in message
...
I have multiple spreadsheets with varying print ranges that change
monthly.
I'm trying to figure out the macro using sendkeys, but not working
very
well.
Any suggestions?

How to write a macro to select print ranges that vary monthly
 

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

for each sh in worksheets loops over all worksheets and does each one.

if you want a subset then

for each sh in Worksheets(Array("Jan","Mar",Jun"))

or if you just want to exlude a sheet or two

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
if sh.name < "ABC" and sh.Name < "Master" then
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
End if
Next
End Sub

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"wilson@irco" wrote in message
...
Tom, I'm pretty much an amatuer here and am not sure where I
identify,insert
the various sheet names within the code you sent. Do I have to put
anything
in the brackets on line one of your message? Any other lines I need to
adjust to customize for my application? Thanks.

"Tom Ogilvy" wrote:

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

Assuming that column A does not contain formulas but contains Data.

--
Regards,
Tom Ogilvy


"wilson@irco" wrote in message
...
Thank you Tom. Unfortunately not that simple. The data does always
start
in
A1, but due to the condition that additional lines are added next mo.
formulas extend beyond this month's print range. There are also
column
breaks (H,L and Q). The end column remains constant (S).

"Tom Ogilvy" wrote:

What determines the extent of the print range?

does the upper left corner never change and can we assume the print
ranges
are contiguous blocks of data separated from other data by the edge
of
the
sheet or blank rows and columns. If so

Worksheets("Sheet2").Select
Range("B9").CurrentRegion.Select


--
Regards,
Tom Ogilvy

"wilson@irco" wrote in
message
...
I have multiple spreadsheets with varying print ranges that change
monthly.
I'm trying to figure out the macro using sendkeys, but not working
very
well.
Any suggestions?

How to write a macro to select print ranges that vary monthly
 

I see now. Thank you for your timely and accurate responses. Can we use
this same range macro to copy each sheet to pdf file? Is there any
additional code you could provide to perform this fn. Thank you.

"Tom Ogilvy" wrote:

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

for each sh in worksheets loops over all worksheets and does each one.

if you want a subset then

for each sh in Worksheets(Array("Jan","Mar",Jun"))

or if you just want to exlude a sheet or two

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
if sh.name < "ABC" and sh.Name < "Master" then
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
End if
Next
End Sub

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"wilson@irco" wrote in message
...
Tom, I'm pretty much an amatuer here and am not sure where I
identify,insert
the various sheet names within the code you sent. Do I have to put
anything
in the brackets on line one of your message? Any other lines I need to
adjust to customize for my application? Thanks.

"Tom Ogilvy" wrote:

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

Assuming that column A does not contain formulas but contains Data.

--
Regards,
Tom Ogilvy


"wilson@irco" wrote in message
...
Thank you Tom. Unfortunately not that simple. The data does always
start
in
A1, but due to the condition that additional lines are added next mo.
formulas extend beyond this month's print range. There are also
column
breaks (H,L and Q). The end column remains constant (S).

"Tom Ogilvy" wrote:

What determines the extent of the print range?

does the upper left corner never change and can we assume the print
ranges
are contiguous blocks of data separated from other data by the edge
of
the
sheet or blank rows and columns. If so

Worksheets("Sheet2").Select
Range("B9").CurrentRegion.Select


--
Regards,
Tom Ogilvy

"wilson@irco" wrote in
message
...
I have multiple spreadsheets with varying print ranges that change
monthly.
I'm trying to figure out the macro using sendkeys, but not working
very
well.
Any suggestions?

How to write a macro to select print ranges that vary monthly
 

Usually creating a pfd file is just a matter of printing, so set the
appropriate print drive, then at the bottom of the macro

sheets.printout

or do sh.printout in the loop if you want separate files.

You will have to specify a name for the file which is an option in the
printout method. See help for details.

--
Regards,
Tom Ogilvy


"wilson@irco" wrote in message
...
I see now. Thank you for your timely and accurate responses. Can we use
this same range macro to copy each sheet to pdf file? Is there any
additional code you could provide to perform this fn. Thank you.

"Tom Ogilvy" wrote:

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

for each sh in worksheets loops over all worksheets and does each one.

if you want a subset then

for each sh in Worksheets(Array("Jan","Mar",Jun"))

or if you just want to exlude a sheet or two

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
if sh.name < "ABC" and sh.Name < "Master" then
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
End if
Next
End Sub

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"wilson@irco" wrote in message
...
Tom, I'm pretty much an amatuer here and am not sure where I
identify,insert
the various sheet names within the code you sent. Do I have to put
anything
in the brackets on line one of your message? Any other lines I need
to
adjust to customize for my application? Thanks.

"Tom Ogilvy" wrote:

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

Assuming that column A does not contain formulas but contains Data.

--
Regards,
Tom Ogilvy


"wilson@irco" wrote in
message
...
Thank you Tom. Unfortunately not that simple. The data does
always
start
in
A1, but due to the condition that additional lines are added next
mo.
formulas extend beyond this month's print range. There are also
column
breaks (H,L and Q). The end column remains constant (S).

"Tom Ogilvy" wrote:

What determines the extent of the print range?

does the upper left corner never change and can we assume the
print
ranges
are contiguous blocks of data separated from other data by the
edge
of
the
sheet or blank rows and columns. If so

Worksheets("Sheet2").Select
Range("B9").CurrentRegion.Select


--
Regards,
Tom Ogilvy

"wilson@irco" wrote in
message
...
I have multiple spreadsheets with varying print ranges that
change
monthly.
I'm trying to figure out the macro using sendkeys, but not
working
very
well.
Any suggestions?

How to write a macro to select print ranges that vary monthly
 

Thanks for all your help on this Tom.

"Tom Ogilvy" wrote:

Usually creating a pfd file is just a matter of printing, so set the
appropriate print drive, then at the bottom of the macro

sheets.printout

or do sh.printout in the loop if you want separate files.

You will have to specify a name for the file which is an option in the
printout method. See help for details.

--
Regards,
Tom Ogilvy


"wilson@irco" wrote in message
...
I see now. Thank you for your timely and accurate responses. Can we use
this same range macro to copy each sheet to pdf file? Is there any
additional code you could provide to perform this fn. Thank you.

"Tom Ogilvy" wrote:

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

for each sh in worksheets loops over all worksheets and does each one.

if you want a subset then

for each sh in Worksheets(Array("Jan","Mar",Jun"))

or if you just want to exlude a sheet or two

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
if sh.name < "ABC" and sh.Name < "Master" then
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
End if
Next
End Sub

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"wilson@irco" wrote in message
...
Tom, I'm pretty much an amatuer here and am not sure where I
identify,insert
the various sheet names within the code you sent. Do I have to put
anything
in the brackets on line one of your message? Any other lines I need
to
adjust to customize for my application? Thanks.

"Tom Ogilvy" wrote:

Sub SetPrintArea()
Dim sh as Worksheet, rng as Range
for each sh in worksheets
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End( xlup))
sh.pageSetup.PrintArea = rng.Resize(,19).Address(external:=True)
Next
End Sub

Assuming that column A does not contain formulas but contains Data.

--
Regards,
Tom Ogilvy


"wilson@irco" wrote in
message
...
Thank you Tom. Unfortunately not that simple. The data does
always
start
in
A1, but due to the condition that additional lines are added next
mo.
formulas extend beyond this month's print range. There are also
column
breaks (H,L and Q). The end column remains constant (S).

"Tom Ogilvy" wrote:

What determines the extent of the print range?

does the upper left corner never change and can we assume the
print
ranges
are contiguous blocks of data separated from other data by the
edge
of
the
sheet or blank rows and columns. If so

Worksheets("Sheet2").Select
Range("B9").CurrentRegion.Select


--
Regards,
Tom Ogilvy

"wilson@irco" wrote in
message
...
I have multiple spreadsheets with varying print ranges that
change
monthly.
I'm trying to figure out the macro using sendkeys, but not
working
very
well.
Any suggestions?