Vb to set print area for dynamic pages
 

I have a workbook with four pages, each with the same number of columns, but
various numbers of rows. I have a macro to automatically set the print area
for those pages and it works just fine. Now I have to add a column to just
one of the pages in the workbook. The macro I have naturally excludes the
new last column. I suspect more changes are on the horizon i.e some pages
growing, others shrinking. I'd rather have Excel 'see' what columns are
present, rather than continually altering the code when I make changes. I
tried to use XLRight instead of naming the column "U", but that doesn't work.
I think I'm on the right track but probably not using the correct syntax.
Here is the code. Any one have any ideas?

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.PageSetup.PrintArea = "A1:U" & LastRow
End With
Next

Vb to set print area for dynamic pages
 

Try this:

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
Dim LastRow As Long
Dim LastCol As String

With ActiveSheet
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With

'Put in the next range selection the column where you have field names,
in this example is A1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
LastCol = Selection.Address
LastCol = Mid(LastCol, InStr(LastCol, ":") + 2)
LastCol = Left(LastCol, InStr(LastCol, "$") - 1)

ActiveSheet.PageSetup.PrintArea = "A1:" & LastCol & LastRow
Next


Regards.


--
dbarelli
------------------------------------------------------------------------
dbarelli's Profile: http://www.excelforum.com/member.php...o&userid=31275
View this thread: http://www.excelforum.com/showthread...hreadid=509454

Vb to set print area for dynamic pages
 

I'm getting a "Select Method of Range Class Failed" error. I put in the
range where I have field names as you suggested. I tried other ranges, but
still get that error.

"dbarelli" wrote:


Try this:

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
Dim LastRow As Long
Dim LastCol As String

With ActiveSheet
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
End With

'Put in the next range selection the column where you have field names,
in this example is A1
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
LastCol = Selection.Address
LastCol = Mid(LastCol, InStr(LastCol, ":") + 2)
LastCol = Left(LastCol, InStr(LastCol, "$") - 1)

ActiveSheet.PageSetup.PrintArea = "A1:" & LastCol & LastRow
Next


Regards.


--
dbarelli
------------------------------------------------------------------------
dbarelli's Profile: http://www.excelforum.com/member.php...o&userid=31275
View this thread: http://www.excelforum.com/showthread...hreadid=509454

Vb to set print area for dynamic pages
 

Be shure to run this code inside a Module. After that if still not
working, really dont know. It works for me.:confused:

Regards


--
dbarelli
------------------------------------------------------------------------
dbarelli's Profile: http://www.excelforum.com/member.php...o&userid=31275
View this thread: http://www.excelforum.com/showthread...hreadid=509454

Vb to set print area for dynamic pages
 

Got it to work, I was being a pinhead. Thanks for your help.

"dbarelli" wrote:


Be shure to run this code inside a Module. After that if still not
working, really dont know. It works for me.:confused:

Regards


--
dbarelli
------------------------------------------------------------------------
dbarelli's Profile: http://www.excelforum.com/member.php...o&userid=31275
View this thread: http://www.excelforum.com/showthread...hreadid=509454