Copy data and page break settings from 1 sheet to another

[Stephen C]

I am using office 2000 on an XP computer.

I am trying to copy data from one sheet to another which works fine but i
also need to copy the page break settings.

I know i can copy the sheet in edit-move or copy, i do not want to do this
as i already have a sheet set for the information to be pasted onto.

Any help would be grateful.

 

[Joel]

Run this macro. It is for horizontal breaks only. I can add vertical breaks
as well

Sub break()

For Each pb In Worksheets(1).HPageBreaks
RowNumber = pb.Location.Row
Worksheets(2).Select
Range("A" & RowNumber).Select

ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
Next pb

End Sub

 

[Stephen C]

Thank you for the reply but i am unable to get this code to work.

I assume that the 1 and 2 in () represents the sheet names.

 

[Joel]

Sheet number or names in double quotes "Sheet1". what is failing, the code
is very simple. I found it only failed if there were no page breaks on the
sheet.

 

[Stephen C]

If i input the correct sheet names in double quotes it seems to find the page
breaks on one sheet and move the cursor to that line on the 2nd sheet but it
does not add the page break in.

This line of code seems to be the problem.

ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell

 

[Stephen C]

Page breaks are on the sheet, i have set them by going to the view menu and
selecting page break preview and then moving them. (e.g. after line 41, 78,
101, 140, 163, 200)

When the marco runs the cursor on the second sheet moves to the cell below
each of these lines but does not add the page break.

 

[Joel]

The code works, but there must be date in the sheet you are adding the page
breaks for it to actually work

 

[Stephen C]

The same data is on both sheets, Cell A1:J235.

Some rows are blank but this should not make any difference.

 

[Stephen C]

Heres the full code

Sub Copy()

Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste
Rows("7:9999").Select
Selection.Interior.ColorIndex = xlNone
Range("A1").Select

For Each pb In Worksheets("Sheet1").HPageBreaks
RowNumber = pb.Location.Row
Worksheets("Sheet2").Select
Range("A" & RowNumber).Select

ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
Next pb

End Sub

 

[Joel]

I don't know why but for each did not work. The index method did work.

Sub Copy()
Dim pb As HPageBreaks
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
Cells.Select
ActiveSheet.Paste
Rows("7:9999").Select
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
Count = Worksheets("Sheet1").HPageBreaks.Count
For Index = 1 To Count
RowNumber = Worksheets("Sheet1").HPageBreaks(Index).Location.Row
Worksheets("Sheet2").Select
Range("A" & RowNumber).Select

ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell
Next Index

End Sub

 

[Stephen C]

I have tried this code and it is doing the same as the other, i have look
into it a little further and it is adding the page breaks in on the 2nd sheet
but it seems to ignor them when you go to page break preview, print preview
or print.

Any ideas why they are being ignored?

I have tried to add a page break by clicking on the insert menu and
selecting page break, this seems to add the page break in but ignors it when
printing, why is this?

 

[Stephen C]

I have just found out why it ignors the page breaks, it is because i have the
page setup as fit to 1 page wide by 10 tall, if i change this to adjust to
100% the page breaks work correctly.

The only way to have it take notice of the page breaks and have the page
setup as fit to is to go to the page break preview and drag them to the
required line.

Thank you for your help.