Programming Questions? Ask a Programmer for Answers ASAP
It would help if you can post your file.
Assume the Groups are in Column A, the names are XXXXX XXXXX B. You are printing a landscape letter size page around line 36 or so is the automatic end of page, Let's say Group Six is at end of page, Group Six gets printed on this page and the names for Group Six are moved to the the next page. I want to keep Group Six together with the names. So I want to be able to set up each Group heading and keep the names together. Thanks.
Although you can have a macro written to automate the process (provided that the macro can recognize where each group title starts) I think it is easier to scroll down the list and manually apply page breaks at the beginning of each group title. Takes a few seconds to do 10's of pages. If you want step by stem instruction let me know.
Hope this helps. Let me know if you have any questions.
I see, you did not make that clear in your question. So you want me to create a macro that will do that automatically?
Are you familiar with macros to install it yourself if I send you the code? If not then I will need your file to set it up for you.
Here you go.
Sub PBreaks()Dim u, i As Integeru = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.CountFor i = 1 To u If Left(Cells(i, 1), 5) = "Group" Then ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(i & ":" & i) End IfNext iEnd Sub
Hope this is ok. Let me know if you have any questions.
Ok, does not seem to work on Excel 2002 not your problem I said 2003. Please wait until later in the day until I can try on Excel 2003. I am going to sleep now.
It should work on ALL versions of Excel. By the same token, if it does not work on one it won't work on any other versions.
There are two possible reasons why it may not have worked on your file:
1) Your Excel Options are not set to allow macros to run.
2) Your data is not structured as per the sample which you provided, Specifically, the word "Group" is not found at the beginning of the phrases in column A (e.g. "Group One", "Group Two", etc).
If you want me to send you my file let me know.
Sorry, did not work. Security allows macros to run, simple file should not be a problem. Will send you a file tomorrow or will tell you why it does not work if i get answer from another source.
It definitely should work, provided of course you have eliminated the two possible reasons that I gave you earlier.
Here is the file with the macro. After you run the macro switch to Page Break Preview to see how the page breaks got set at each Group heading
Here is also a screenshot of the Page Break Preview after running the macro. If you do not see this when you run the macro then your options are set not to allow macros to run.
Let me know how it goes.
Thanks John D. I am not trying to be difficult and I think I will pay you for trying to solve my problem.
Here is the thing. Let's use "Teams" instead of "Groups". Column A1 wilI have Team 1 then in Column B starting at B2 will be the team members. B2 through BX. Could be 2 people on a team or 5 people.
I don't want each Team on a page. I want to fit as many teams as I can on a page, I just dont want to see "Team X" on the bottom of the page and the team members on the next page. I want to keep "Team X" with its corresponding team members.... do you understand what I mean..... no widows.
Thanks again for trying.
Thanks for making it clear and of course for the accept as well.
Now that I know exactly what you are looking for I will look at it tomorrow and try to get the macro to do that. Cannot promise success but I will try. Will keep you posted.
Just letting you know that I am still working on it and will probably finish it by noon tomorrow. It is turning out to be quite tricky because the subsequent page brakes keep changing every time a new page brake is introduced at a title row.
Thanks, XXXXX XXXXX imagine the logic might be something like:
Clear all page breaks, (assume the print range is set or maybe force user to set print range??) examine the first automatic page break. Is there a value in Column A at page break row, if so put page break before, if not do nothing. Examine the second and so forth and so on.
Here are some useful links I looked at: www.ozgrid.com/Excel/excel-conditional-page-breaks.htm
Ok, I think I got it... whew!
I had to use a combination of vba code, defined names formulas, and cell formulas (which are placed in a helper column AA and which of course can be hidden if you do not want it to show).
Hope it is ok.