Macro - Set Print Area for Changing Data Area

Hi All

I have an Excel 2003 spreadsheet that has data that is dumped from another application. I wanted to automate setting the print area each time the user creates the file, my problem is that the amount of data is not fixed and will change everytime they update the info.

I can't assume that there will always be text in the last cell in the bottom right hand corner as this cell may be blank. However, the spreadsheet print area will always be 7 columns wide, and the last cell in Column D will always have data.

Does anybody have any idea o fhow to do this ?

Ta

Karen

Hi Leigh

Thanks for the info (Good assumption - yes it was columns A to G)

I have copied and pasted your code into a new module, but when I try to run the macro I get a run time error '13', type mismatch

If I click on debug this is the line that is highlighted

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

Are you able to shed any light on this ?

Thanks

Karen

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

You're a champion - it works a treat

Thanks heaps for that I really appreciate it

Ta

Karen

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
>
>

Hi Leith,

i am new to VB thus needed some help for excel macro. I found below code in this thread which i was looking for howevr it runs only on active sheet. This code is amazing.
----------------------------------------------------------------------------------------------------
Public Sub AutoSetPrintArea()

Dim LastRow As Long

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

End Sub
----------------------------------------------------------------------------------------------------

Can you do me a favour and suggest me a macro which can set print area for all sheets of workbook.
It will be like separate macro file which I can run on other excel files to set print area for all sheets of workbooks

Can you please help me in this.
Thank you.

Amogh

Re: Macro - Set Print Area for Changing Data Area