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

wilson@irco · 02-24-2006, 12:10 PM

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?

Tom Ogilvy · 02-24-2006, 12:50 PM

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" <wilson@[email protected]> wrote in message
news:[email protected]...
> 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?

wilson@irco · 02-24-2006, 01:15 PM

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" <wilson@[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

Tom Ogilvy · 02-24-2006, 02:00 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <wilson@[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> >
> >
> >

wilson@irco · 02-24-2006, 02:50 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <wilson@[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
> > >
> > >
> > >
>
>
>

Tom Ogilvy · 02-24-2006, 04:10 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <wilson@[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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?
> > > >
> > > >
> > > >
> >
> >
> >

wilson@irco · 02-24-2006, 04:35 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <wilson@[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Tom Ogilvy · 02-24-2006, 06:50 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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" <wilson@[email protected]> wrote in
> > message
> > > > > > news:[email protected]...
> > > > > > > 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?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

wilson@irco · 02-27-2006, 01:10 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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" <wilson@[email protected]> wrote in
> > > message
> > > > > > > news:[email protected]...
> > > > > > > > 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?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>