| 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
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. |
|
#6
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. |
|
#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. |
| 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) | |||
Hybrid Mode
