| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data and page break settings from 1 sheet to another
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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 befo=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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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 befo=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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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 befo=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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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 befo=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 befo=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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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 befo=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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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 befo=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 befo=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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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 befo=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 befo=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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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 befo=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 befo=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 befo=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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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.R ow Worksheets("Sheet2").Select Range("A" & RowNumber).Select ActiveWindow.SelectedSheets.HPageBreaks.Add befo=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 befo=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 befo=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 befo=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. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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? "Joel" wrote: 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.R ow Worksheets("Sheet2").Select Range("A" & RowNumber).Select ActiveWindow.SelectedSheets.HPageBreaks.Add befo=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 befo=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 befo=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 befo=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. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy data and page break settings from 1 sheet to another
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. "Joel" wrote: 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.R ow Worksheets("Sheet2").Select Range("A" & RowNumber).Select ActiveWindow.SelectedSheets.HPageBreaks.Add befo=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 befo=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 befo=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 befo=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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| page break settings for excel worksheet | Setting up and Configuration of Excel | |||
| Detect page break settings | Excel Worksheet Functions | |||
| How can I Copy "Page Setup" Settings between Workbooks | Excel Discussion (Misc queries) | |||
| How do I change default settings to page break preview | Setting up and Configuration of Excel | |||
| How can I copy page setup settings to other worksheets? Thanks | Excel Discussion (Misc queries) | |||
Linear Mode
