Excel 2000 Carrying Specific Text Over To Next Worksheet When Past Pagebreak

Hello,

Please see attached sample.  In this sample several departments are listed and below those departments are numbers representing rows of data being used for each department.

The problem is when you look at the page breaks and get to the last department on the 1st page, in this case, Department 3, we have rows of data that have to go on to page 2 due to the pagebreak.  Is there a way (vba code?) that will look at the last department on the page, copy and paste that department name to the top of the 2nd page and then have the remaining data rows below it?  Therefore, at the bottom of page 1 it would appear as:

Department 3
1
2
3
4
5
6
7
8
9
10
11
12
13

and on page 2, it would then appear as follows:  

Department 3
14
15
16
17
etc.

The print titles to repeat at the top wont work since the range will always be different from page to page.  Basically need a code that if the rows exceed past the page break it will copy and paste the department name at the top of the following page followed by the remaining rows of data.

Is this possible?  Thanks!

Test-File.xls
LVL 1
EscanabaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hitsdoshi1Commented:
There you go....

Please note I have added "End" at the end of the page as identifier (to stop my do while loop).....and also you had department 2 spelled wrong.....so make sure "Department" is always spelled correct to determine the page break...

Also, for right now I have page break for 10 columns which you can change it to whatever you want...

Good Luck !



Sub pbreaks()
i = 2
ActiveSheet.ResetAllPageBreaks
Do While Cells(i, 1) <> "End"
    If Left(Cells(i, 1), 10) = "Department" Then
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(i - 1, 10)
    End If
    i = i + 1
    Debug.Print i
Loop
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(i - 1, 10)
End Sub


Test-File.xls
0
EscanabaAuthor Commented:
Thanks for the quick response.  Unfortunately  keep getting an object defined error at this point:       Do While Cells(i, 1) <> "End"
Code has been added to the attached file.

Test-File.xls
0
hitsdoshi1Commented:
Couple of things.......

1) you need to put "End" as identifier at the end

2) On the 2nd last line....before End Sub and After Loop.....Replace i-1 to i in Cells(i,10)....it should read as following...its working fine.....I just tested it...
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(i, 10)

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

EscanabaAuthor Commented:


Sorry...still having problems.  This takes care of the error but when I run the code it's not formatting the data correctly.  For example, on the attached file, when I run the code Department 1 runs beyond page 1.  What it should do at the beginning of page 2 is display as follows:
Department 1
52
53
54
etc.
Instead, its doing a page break at row 53-58 and then starts another new page starting with Department 2.  Is there something I'm doing wrong here?

Test-File.xls
0
hitsdoshi1Commented:
That is cos you are using page breaks and data is going beyond the page.....I think the max data page can take is like 50 rows.....if data goes beyond that it goes to other page....

Going back to your original question about new dept to start from new page is working.......now regarding this issue..you can try adjusting margin or fonts......

So with your file what I did is in Page Setup - I adjusted the margins to 0.25 from 1.00 and font size reduced to 8 from 10 and it works fine.


Test-File.xls
0
hitsdoshi1Commented:
If data is more, you can also trying changing paper size to Legal from Letter in Page Setup....
0
EscanabaAuthor Commented:
"That is cos you are using page breaks and data is going beyond the page.....I think the max data page can take is like 50 rows.....if data goes beyond that it goes to other page...."
I agree.  What I as hoping to have this code do is when the data exceeds the max, lets say 50 rows, that the code will put the departments name in the first row of the 2nd page, so we know what department the following data is for, followed by the remaining data in a standard letter size page format.  Therefore, on the sample file, when the data exceeds row 50, the top of page 2 would appear as follows:
Department 1
52
53
54
etc.
Basically I want to somehow inject the department name at the top of the next page before the data carriers over.  Ideally, if the department name did not change, then the "print titles to repeat at the top" function would work out perfectly.  Instead, the department name will change so the range is variable.
0
hitsdoshi1Commented:
There you go.....what I am doing in this is forcing page break every 50 rows if data exceeds more then 50 rows for the same dept and inserting the department name on the 51st row which would be top on the next page....

Hope this helps...


Test-File.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EscanabaAuthor Commented:
That got it!  Thanks for all your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.