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?
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?
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?
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?
>
>
>
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, 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?
> > >
> > >
> > >
>
>
>
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?
> > > >
> > > >
> > > >
> >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks