How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9659
Experience:  Vast experience in Excel programming and business solutions
53785
Type Your Programming Question Here...
John D is online now
A new question is answered every 9 seconds

Using Excel 2003. In Word you have a keep with next function

Customer Question

Using Excel 2003. In Word you have a "keep with next" function to keep text together. How can you create an Excel function or macro so that if you print a spreadsheet you can dynamically control the printing so that if the heading of a row is at the end of the page and the corresponding information is pushed to the next page you can keep the heading with the corresponding text. Note I don't want to print a new page every time the heading changes. I want a full page of data but don't want widows or orphans so to speak.
Submitted: 5 years ago.
Category: Programming
Expert:  John D replied 5 years ago.

Hi,

 

It would help if you can post your file.

 

 

 

 

 

 

Expert:  John D replied 5 years ago.

Hi,

 

It would help if you can post your file.

 

 

 

 

 

 

Customer: replied 5 years ago.

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.

 

Group One

XXXXX XXXXX

John Doe

Jean Simms

 

Group Two

Adam Fox

Alex Woods

Joe Shmo

 

Group Three

Jeff Shine

Ally McBeal

Jane Austin

Expert:  John D replied 5 years ago.

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.

 

Customer: replied 5 years ago.
Thanks for your response. I know I can scroll down and set page breaks but that is not acceptable.
Expert:  John D replied 5 years ago.

I see, you did not make that clear in your question. So you want me to create a macro that will do that automatically?

 

 

Customer: replied 5 years ago.
Using Excel 2003. In Word you have a "keep with next" function to keep text together. How can you create an Excel function or a macro so that if you print a spreadsheet you can dynamically control the printing so that...you can keep the heading with the corresponding text.
Expert:  John D replied 5 years ago.

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.

 

 

Customer: replied 5 years ago.
You can just send the code. Thanks.
Expert:  John D replied 5 years ago.

Here you go.

 

Code:

Sub PBreaks()
Dim u, i As Integer
u = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
For i = 1 To u
    If Left(Cells(i, 1), 5) = "Group" Then
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rows(i & ":" & i)
    End If
Next i
End Sub

 

Hope this is ok. Let me know if you have any questions.

 


Customer: replied 5 years ago.
Thanks. I will let you know if it works by accepting at that time.
Expert:  John D replied 5 years ago.

No problem.

 

 

Customer: replied 5 years ago.

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.

 

Thanks.

Expert:  John D replied 5 years ago.

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.

 

OR

 

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.

 

 

Customer: replied 5 years ago.

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.

Expert:  John D replied 5 years ago.

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

 

FILE

 

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.

 

graphic

Let me know how it goes.

 

John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9659
Experience: Vast experience in Excel programming and business solutions
John D and 7 other Programming Specialists are ready to help you
Customer: replied 5 years ago.

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.

Expert:  John D replied 5 years ago.

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.

 

 

Expert:  John D replied 5 years ago.

Hi again,

 

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.

 

 

 

 

Customer: replied 5 years ago.

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

www.ozgrid.com/forum/showthread.php?t=32331

Expert:  John D replied 5 years ago.

Ok, I think I got it... whew!

 

FILE

 

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.

 

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
< Last | Next >
  • http://ww2.justanswer.com/uploads/SP/spatlanta2010/2011-6-23_12450_photo.64x64.gif ATLPROG's Avatar

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Computer Software Engineer

    Satisfied Customers:

    5603
    Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.
  • http://ww2.justanswer.com/uploads/unvadim/2010-11-15_210218_avatar.jpg unvadim's Avatar

    unvadim

    Computer Software Engineer

    Satisfied Customers:

    1158
    Good knowledge of OOP principles. 3+ years of programming experience with Java and C++. Sun Certified Java Programmer 5.0.
  • http://ww2.justanswer.com/uploads/lifesaver333/2010-10-17_191349_ls.jpeg lifesaver's Avatar

    lifesaver

    Computer Software Engineer

    Satisfied Customers:

    950
    Several years of intensive programming and application development experience in various platforms.
  • http://ww2.justanswer.com/uploads/EH/ehabtutor/2012-8-2_202016_1.64x64.jpg ehabtutor's Avatar

    ehabtutor

    Computer Software Engineer

    Satisfied Customers:

    864
    Bachelor of computer science, 5+ years experience in software development, software company owner
  • http://ww2.justanswer.com/uploads/RA/rajivsharma086/2012-6-6_17128_displaypic.64x64.jpg Raj's Avatar

    Raj

    Computer Engg.

    Satisfied Customers:

    860
    BE CS, 4+ Experience in Programming and Database (ERP)
  • http://ww2.justanswer.com/uploads/eljonis/2010-01-06_130406_eljon2.jpg Eljon's Avatar

    Eljon

    Consultant

    Satisfied Customers:

    590
    11 yrs of programming (PHP, WordPress, XSL, SQL, JavaScript)