Macro - Set Print Area for Changing Data Area

  • Thread starter Thread starter ksp
  • Start date Start date
K

ksp

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
 
Hello Karen,

You don't say which 7 columns you are using, so I am going to use A t
G in this example. You can change it later if you need to. Insert a VB
module into your workbook and paste this code into it. You can create
button to call this macro for you, or simply run it from the Macro Lis
by pressing ALT + F8 and selecting it.


Code
-------------------
Public Sub AutoSetPrintArea()

Dim LastRow As Long

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

End Sub
 
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 said:
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


--
Hi Leith,

This code is great. However it is running on active sheet and not for all sheets in workbook.
Can you please suggest or provide code which will run on all sheets of workbook by run once. Awaiting your reply.

Thank you
Amogh
Click to expand...
 
I have been having a similar issue with trying to define a print area. the area i need to print changes with each time i refresh the data.

Currently i have set a print area manually but this wont work when i start getting more data.

I need to add/change my code in someway so that it only prints the pages of the worksheet which have data in them.

Annoyingly each page of the sheet is a table and so has headers for each column which any function would, i assume, class as data and so i only want them to print if the table gas data.
The good news is that if the first row in the table has data in then i need that whole page printed anyway.

my current code is:

Dim PathString As String

Sub Print_ActionManager_1_RecentActivityReport()

Application.ScreenUpdating = False

Sheets("Districts").Select
Range("A2").Select


While Not ActiveCell.Value = Empty

Sheets("RecentActivityReport").Range("N1").Value = ActiveCell.Value
Sheets("RecentActivityReport").Select
'Call ChangeGraphAxis
Range("A4").Select
Sheets("RecentActivityReport").Select
Application.StatusBar = "Executing - Please be patient..."
Call Print_To_File3(Sheets("RecentActivityReport").Range("N1").Value)
Application.StatusBar = False
Sheets("Districts").Select
ActiveCell.Offset(1, 0).Select

Wend

Sheets("RecentActivityReport").Select
Application.ScreenUpdating = True

Sheets("RecentActivityReport").Select
Range("A3").Select

MsgBox "All Completed, Please check Files!"

End Sub


Function Print_To_File3(Districts As String)

'PathString = "\\Ascurbansci11\GM\GME\S4 Navigate\Action Manager\Reports\France\Reporting\DistrictReports\1_RecentActivityReport" & DealerGroup & ".pdf"
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, PrintToFile:=True, PrToFileName:=PathString

CurrentDir = ActiveWorkbook.Path

Application.DisplayAlerts = False

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
CurrentDir & "\Reporting\DistrictReports\1_RecentActivityReport\District_Action_Report_" & Districts & "_1_RecentActivityReport_" & Format(Now, "dd-mm-yy") _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

Application.DisplayAlerts = True

End Function

any help would be much appreciated.
 
Back
Top