Recognizing Specific Text

[olebucky]

It's been a while but I am back again. I am playing with the same cards; a solid excel user and a psuedo-neophyte in VBA.

I am trying to automate the pagination of a report compiled by combining sub-reports generated from a large data set on another sheet in the same workbook. My issue is I cannot get VBA to recognize the content of a specific cell then apply a page break to the blank cell above the specific cell.

This is a screen grab of where the code below does not work. The page break at row 348 is an auto page break and is not replace by a hard page break in the blank cell in the row above.

Capture.PNG


The following is my short macro I cobbled together without shame of plagiarizing. I believe the If rng.Value = "Report Date:" Then code line is the issue possibly stemming from a total lack of syntax wisdom or some misguided declarative statements above.

Please Login or Register  to view this content.

The basic repagination logic is based on the predetermined format of the sub-reports and is:

If the automatic page break is on a blank cell, that is good, skip out.

Else if the automatic page break falls at a cell containing the text "Report Date:", put a page break in the blank cell immediately above the current then on to the next auto page break

Else if the auto page break cell text is anything other than "Report Date:", CRTL up once to the cell below the next blank up

If that cell text is "Report Date:", put a page break in the blank cell immediately above the current then on to the next auto page break

Many regrets on the long post but I felt my distilled question might be lost without sufficient explanation. In advance, thanks to the entire for any help offered here and for all the times I've signed on and found necessary solution without posting.

[protonLeah]

One option is to insert manual page breaks before the "report date" row; but let Excel auto page break the sub reports as/if necessary:

Please Login or Register  to view this content.

[olebucky]

Thanks so much for the suggestion. I will give it a try and report back.

[olebucky]

Thank you for your input, but I think I did a poor job describing the full scope of my issue.

The code you offered works great for setting a page break at the top of each sub-report (I ran . However, my goal is to have multiple sub-reports on one page but to not have any of the "Report Date:" sub-reports split by a page break. Further, I want to have the page break set at the blank cell above the cell containing "Report Date:".

For example, the following are two example of unwanted automatic page breaks:

Incorrect Auto Page Break 1_20141117.jpg

2_Incorrect Auto Page Break_20141117.jpg

Respectively, the code should put a page break on rows 105 and 317 for the above examples. Had one of the auto-page breaks fallen on rows 105 or 317 (the rows with blanks in column A), there is no need to reset the page break.

To add to the complication, there is another type of sub-report the data set can place into the overall report. Its top row in column A is "Flow Test". These sub-reports can be long and therefore are allowed to break across pages. Hence the need to differentiate between the top row of a sub-report and skip the sub-reports that are not topped with "Report Date:".

Wow! Another long-winded post. Hopefully it will help you and the rest of the forum better understand my issues.

[protonLeah]

Please post a workbook with the "report date column (A?)" that determines the page breaks?
To attach a Workbook

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[olebucky]

Will do. It might be a day or two if I can't get to it by tomorrow AM. Thanks so much.

[olebucky]

The data sheet (which was quite large) is no longer with the workbook. The reports have all (I hope) the formulas converted to values. The workbook has one sheet with automatic page breaks and a second sheet with manual page breaks I added as I would like the macro to set automatically.

I have two or three of these per day and they sometimes are 30 or 40 pages long. Resetting the page breaks manually is too time consuming.

Thanks in advance.

[protonLeah]

Option Explicit
The code GetPageBreaks() only seems to work from a sheet module. I couldn't get it to work from a standard module
so it has to be in the module of the sheet to be repaginated.

Please Login or Register  to view this content.

The code below puts pagebreaks at the top and bottom of each "flow test" section and leaves any auto pages breaks within
the section as is. It moves pgbks in the "report date" blocks to the top of the previous "report date cell"

Please Login or Register  to view this content.

[olebucky]

Wow! Looks VERY interesting. I will run it with my worksheet and hopefully learn something too. Report to follow. (Regrets on the tardy reply. Lots of projects pulling me in multiple directions.)

[olebucky]

Sorry, but I can't get the macro in the downloaded workbook to work correctly. It is possible I am not using it correctly. I simply downloaded the workbook as attached to the post and ran the macro (through both the ribbon interface from Excel and via the VBA editor). Is there something else I need to do? Regrets on my ignorance.

I did run it multiple times and found a definite pattern in the results. If it turns out I implemented and ran the macro correctly, I will share what I observed. If I did not implement or run the macro correctly, the observations are meaningless, hence saved for the appropriate time.

[protonLeah]

The workbook attached has two sheets:
tab name: RptSumCompl Auto PB (vba codename: Sheet4)
tab name: RptSumCompl Manual PB (vba codename: Sheet5)

In order to paginate RptSumCompl Manual PB, I had to put the GetPageBreaks() in the sheet5 module. Then, in the main sub, InsertPageBreaks() there are two lines that call GetPageBreaks, to load the array of pagebreaks

Call Sheet5.GetPageBreaks

Unfortuntely, I was not able to get "GetPageBreaks" to run from a standard code module. So, in order to get the code to work, you must put the GetPageBreaks code in the sheet module of the sheet to be repaginated. You must then change the code: Call Sheet5.GetPageBreaks to indicate the sheet in your workbook:

Call Sheetn.GetPageBreaks
in two places.

[olebucky]

Thanks again for your time. I will give it another try and report back.

[olebucky]

I am still not able to get your code to run properly. Your explanation was extremely helpful and I think I understand what is supposed to happen, however, when I run the code in "Sheet 5" (i.e. I did not change your code), I get strange results that are the same on multiple runs of the code after resetting the page breaks to all automatic breaks each time:

Page breaks for the "Flow Test" sub-reports are hard breaks and placed as you described but the balance of the page breaks are automatic breaks

The automatic breaks are not coming in as desired.

On rows 214, 242, 366 and 492, the code changes the contents of the column A cell to "Report Date:"

Then, in an attempt to follow your instructions and re-paginate the other sheet, I moved GetPageBreaks() to the module for sheet 4, changed the code in the main sub InsertPageBreaks() and attempted to run the main sub on the appropriate sheet. The following error resulted:

VBA_PB Error_20141204a.jpg


The debugger broke here:

VBA_PB Error_20141204.jpg

At this point I am stumped. If this is getting to be too much of a hassle for you, I would completely understand. Just say "no mas" and we can let this one lie. If so, thanks for all the time and effort invested in this issue. Otherwise, I will keep plugging too.