Setting the print area and header row issues

[darvistor]

Hello all,

I tried searching through the forums and didn't see the exact issue I have so here goes.

I have some data that on a monthly basis gets copied to another workbook and saved. I am trying to automate the process of doing the data copy, file saving and page formatting.

So far I have written the code that copies the data over, saves the file and part of the page formatting. The data is for a 4 month interval with 7 columns of data for each month. The issue I have is that the number of rows can vary from month to month causing data to continue to a second page. I specified a page header and I want the column names to repeat on any additional pages. The goal is to have each month have its data on separate pages in landscape. The data will always be 4 pages wide and X pages long.

Problem 1: I have placed the vertical page breaks where needed to separate the months, but I don't know how to easily determine the number of pages in length or set the print area based on the maximum row used for any month.

Problem 2: Is there a way to prevent the page header and column headers from repeating on pages that don't have any data. I set the pages tall = 20 to handle a large amount of data, but I end up with blank pages with nothing but the header information. If I set the number smaller, then the data is too small to read.

Here is the code I have so far for the page formatting

With Sheets("Sheet1").PageSetup
        .PrintTitleRows = "$1:$3"
        .PrintTitleColumns = ""
    End With
    Sheets("Sheet1").PageSetup.PrintArea = ""
    With Sheets("Sheet1").PageSetup
        .LeftHeader = ""
        .CenterHeader = "&""Arial,Bold""&18" & CStr(cmonth + " " + "fname")
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 4
        .FitToPagesTall = 20
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.ResetAllPageBreaks
    Set ActiveSheet.VPageBreaks(1).Location = Range("H1")
    Set ActiveSheet.VPageBreaks(2).Location = Range("O1")
    Set ActiveSheet.VPageBreaks(3).Location = Range("V1")

Any help is welcome (nudges Skip )
Thanks
Darvistor

 

[SoCalAccessPro]

Hi Darvistor.

To prevent any errors, I'd change all occurences of ActiveSheet to Sheets("Sheet1"). But that's not what you're asking.

As you pointed out, your problem is here, because you never know where the breaks should go:

Set ActiveSheet.VPageBreaks(1).Location = Range("H1")
Set ActiveSheet.VPageBreaks(2).Location = Range("O1")
Set ActiveSheet.VPageBreaks(3).Location = Range("V1")

With a little experimentation, you can find EXACTLY how many lines you can get on page. IIRC, it's right around 52 depending on header, footer, font size etc.

Then, all you have to do is determine the last row in the spreadsheet.

This is done like this.

intLastRow = Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row

Then, just do a simple for next loop. (Psuedo code to follow)

For i = (firstrow) to intLastRow
   If i/52 (or whatever your last row is) = exacty 1 the
     set your page break
    End If
Next i

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[darvistor]

Well thats a start at least. Hmmm, the issue I get now is that if one month doesnt have the same number of rows its 2nd or 3rd page doesnt have data. I end up with a page with the page header and the column names, which was problem number 2. I am using the intlastrow to set my print area as well.

 

[SkipVought]

"Problem 1: I have placed the vertical page breaks where needed to separate the months, but I don't know how to easily determine the number of pages in length or set the print area based on the maximum row used for any month."

File > Page Setup > Sheet TAB - select the ROWS and/or COLUMNS you want to repeat.

"Problem 2: Is there a way to prevent the page header and column headers from repeating on pages that don't have any data. I set the pages tall = 20 to handle a large amount of data, but I end up with blank pages with nothing but the header information. If I set the number smaller, then the data is too small to read."

Your workbook design is severely flawed. Consequently, you get blank pages when you report.

You ought to be using the built-in features of Excel to STORE & REPORT the data correctly, using the specific criteria required, date period, for instance.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[darvistor]

Yes, everything is very easy to do manually. Of course I can look at the data and put the page breaks where I want them. Yes, I can look where the data ends and set my print area. All of this can be done after the fact by the user. What I attempted to do was build the code to do it all automatically so that all they have to do is print it when the report runs. The data is what it is and the workbook is what it is. Those two things I don't have as much freedom to change so I'm trying to figure out a way for it to work, despite whatever flaws it may have.

File > Page Setup > Sheet TAB - select the ROWS and/or COLUMNS you want to repeat.

This wasn't an issue. I have already established what rows to repeat in the code.

 

[SkipVought]

Sorry.

"Problem 1: I have placed the vertical page breaks where needed to separate the months, but I don't know how to easily determine the number of pages in length or set the print area based on the maximum row used for any month."

This is where your design is flawed. If your source data were STORED as a proper table, then you could REPORT whatever month or other time period is required, probably in a matter of seconds or minutes. As it is now, your sheet is mucked up with chunks of data. Without a detailed analysis of the layout, thereby knowing if its possible to tag each chunk with a unique value that could isolate it in a report, I have no way of knowing what to suggest.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[darvistor]

Well my suggestion to the end user was to put each "chunk" (month) of data on its own worksheet. If one month had more rows of data then it could go to a second page without the other months printing on multiple pages. All they have to do is print the entire workbook to get the same data report. I was told that this is what the vendor is used to seeing so I couldnt change it. All the data had to appear on a single sheet.

The data is structured as follows:
Sheet 2 has the data for a 4 month interval
Month 1's data appears in columns A:G
Month 2's data appears in columns H:N
Month 3's data appears in columns O:U
Month 4's data appears in columns V:AB

This format doesn't change, which is why I set the vertical page breaks where I did. The only thing that changes is how many rows each chunk of data has.

Say as pointed out before that 52 rows could fit on a page. Well for this month's report, Month 1 has 72 rows, month 2 has 55 rows, and months 3&4 have 5 rows. So I set the print area to $A$1:$AB$72. Month 1 & 2 look ok but I end up with two pages for Months 3&4 with only the page header and column headers.

I don't see a way around this with all the data on one sheet and setting the print area based on the last row of data. There will always be a section of the print area for those months that have no data.

 

[SkipVought]

Yup!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[darvistor]

Which part are you agreeing with there?

 

[SkipVought]

"There will always be a section of the print area for those months that have no data. " YUP!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]