[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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
0
Escanaba
Asked:
Escanaba
  • 5
  • 4
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
EscanabaAuthor Commented:
That got it!  Thanks for all your help!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now