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.

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" wrote:

> 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.
>

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" wrote:

> 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" wrote:
>
> > 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.
> >

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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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.
> > >

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


"Joel" wrote:

> 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" wrote:
>
> > 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.
> >

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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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.
> > > >

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

"Stephen C" wrote:

> 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
>
>
> "Joel" wrote:
>
> > 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" wrote:
> >
> > > 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.
> > >

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

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

"Joel" wrote:

> The code works, but there must be date in the sheet you are adding the page
> breaks for it to actually work
>
> "Stephen C" wrote:
>
> > 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
> >
> >
> > "Joel" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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.
> > > >

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" wrote:

> The code works, but there must be date in the sheet you are adding the page
> breaks for it to actually work
>
> "Stephen C" wrote:
>
> > 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
> >
> >
> > "Joel" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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.
> > > >

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" wrote:

> 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" wrote:
>
> > The code works, but there must be date in the sheet you are adding the page
> > breaks for it to actually work
> >
> > "Stephen C" wrote:
> >
> > > 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
> > >
> > >
> > > "Joel" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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.
> > > > >