Excellent post, I used this to solve a problem I was having and wanted to
share a variation.

I have a similar situation except I copy many different bunches of data to
many different worksheets. The following function will leave the existing
print settings the same (assuming you set them up once), but changes the end
row of the print range to fit the current data set.

Sub UpdatePrintAreas()
'example function call, I use range names because the worksheet
names/positions change

AutoSetPrintArea Range("CopyFunctionData")
AutoSetPrintArea Range("CopyProcessData")
AutoSetPrintArea Range("CopyFinancialData")
End Sub


Function AutoSetPrintArea(MyRange As Range)

Dim LastRow As Long

With MyRange.Worksheet
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.PageSetup.PrintArea = Left(.PageSetup.PrintArea,
Len(.PageSetup.PrintArea) - 2) & LastRow
End With

End Function



"Leith Ross" wrote:


Hello Karen,

Sorry about that. I made a typo. I'll correct the post also.

Change that line to...
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=487498