Page1 to Page2
 

I have a worksheet with over 300 pages that I need to print.
However the second page can be copied and pasted to the
first page to the adjacent columns and the page layout would work fine.

What I need is a macro to take page 2 and automatically
paste it to page 1 then delete the page 2 info and move up
the empty spaces from the worksheet and then continue
the process till all pages has been done.

In other words:
Page 1 plus page 2 now equals page 1
Then after moving up cleared cells
Page 2 and page 3 now equals page 2 (formally page 3 and 4)
Then after moving up cleared cells
Page 3 and page 4 now equals page 3 (formally page 5 and 6)etc..etc.etc

Involved are 4 columns for page 1 then 5 additional columns
for the new page to be added.5th column would be spacer

I can handle the spacing of the coulombs and rows and resizing the page
widths.

Thanks

Page1 to Page2
 

Sub Move_Sets_PBreak()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "f")

iSource = iSource + 100
iTarget = iTarget + 50

PageBreak = xlPageBreakManual
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord Dibben MS Excel MVP

On Sun, 25 Jan 2009 09:10:36 -0700, "Stanley Braverman"
wrote:

I have a worksheet with over 300 pages that I need to print.
However the second page can be copied and pasted to the
first page to the adjacent columns and the page layout would work fine.

What I need is a macro to take page 2 and automatically
paste it to page 1 then delete the page 2 info and move up
the empty spaces from the worksheet and then continue
the process till all pages has been done.

In other words:
Page 1 plus page 2 now equals page 1
Then after moving up cleared cells
Page 2 and page 3 now equals page 2 (formally page 3 and 4)
Then after moving up cleared cells
Page 3 and page 4 now equals page 3 (formally page 5 and 6)etc..etc.etc

Involved are 4 columns for page 1 then 5 additional columns
for the new page to be added.5th column would be spacer

I can handle the spacing of the coulombs and rows and resizing the page
widths.

Thanks

Page1 to Page2
 

Thank You. That worked very well.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Sub Move_Sets_PBreak()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "f")

iSource = iSource + 100
iTarget = iTarget + 50

PageBreak = xlPageBreakManual
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord Dibben MS Excel MVP

On Sun, 25 Jan 2009 09:10:36 -0700, "Stanley Braverman"
wrote:

I have a worksheet with over 300 pages that I need to print.
However the second page can be copied and pasted to the
first page to the adjacent columns and the page layout would work fine.

What I need is a macro to take page 2 and automatically
paste it to page 1 then delete the page 2 info and move up
the empty spaces from the worksheet and then continue
the process till all pages has been done.

In other words:
Page 1 plus page 2 now equals page 1
Then after moving up cleared cells
Page 2 and page 3 now equals page 2 (formally page 3 and 4)
Then after moving up cleared cells
Page 3 and page 4 now equals page 3 (formally page 5 and 6)etc..etc.etc

Involved are 4 columns for page 1 then 5 additional columns
for the new page to be added.5th column would be spacer

I can handle the spacing of the coulombs and rows and resizing the page
widths.

Thanks

Page1 to Page2
 

Good to hear.

Thanks for the feedback

On Sun, 25 Jan 2009 10:28:10 -0700, "Stanley Braverman"
wrote:

Thank You. That worked very well.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Sub Move_Sets_PBreak()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "f")

iSource = iSource + 100
iTarget = iTarget + 50

PageBreak = xlPageBreakManual
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord Dibben MS Excel MVP

On Sun, 25 Jan 2009 09:10:36 -0700, "Stanley Braverman"
wrote:

I have a worksheet with over 300 pages that I need to print.
However the second page can be copied and pasted to the
first page to the adjacent columns and the page layout would work fine.

What I need is a macro to take page 2 and automatically
paste it to page 1 then delete the page 2 info and move up
the empty spaces from the worksheet and then continue
the process till all pages has been done.

In other words:
Page 1 plus page 2 now equals page 1
Then after moving up cleared cells
Page 2 and page 3 now equals page 2 (formally page 3 and 4)
Then after moving up cleared cells
Page 3 and page 4 now equals page 3 (formally page 5 and 6)etc..etc.etc

Involved are 4 columns for page 1 then 5 additional columns
for the new page to be added.5th column would be spacer

I can handle the spacing of the coulombs and rows and resizing the page
widths.

Thanks

Page1 to Page2
 

I am having a problem with the splitting order. If I use 50 rows per page
then the split and sorting order is ok. But if I want 42 rows per page then
the splitting order is out of proper sort.
Thanks


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Good to hear.

Thanks for the feedback

On Sun, 25 Jan 2009 10:28:10 -0700, "Stanley Braverman"
wrote:

Thank You. That worked very well.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Sub Move_Sets_PBreak()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "f")

iSource = iSource + 100
iTarget = iTarget + 50

PageBreak = xlPageBreakManual
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord Dibben MS Excel MVP

On Sun, 25 Jan 2009 09:10:36 -0700, "Stanley Braverman"
wrote:

I have a worksheet with over 300 pages that I need to print.
However the second page can be copied and pasted to the
first page to the adjacent columns and the page layout would work fine.

What I need is a macro to take page 2 and automatically
paste it to page 1 then delete the page 2 info and move up
the empty spaces from the worksheet and then continue
the process till all pages has been done.

In other words:
Page 1 plus page 2 now equals page 1
Then after moving up cleared cells
Page 2 and page 3 now equals page 2 (formally page 3 and 4)
Then after moving up cleared cells
Page 3 and page 4 now equals page 3 (formally page 5 and 6)etc..etc.etc

Involved are 4 columns for page 1 then 5 additional columns
for the new page to be added.5th column would be spacer

I can handle the spacing of the coulombs and rows and resizing the page
widths.

Thanks

Page1 to Page2
 

Stanley emailed me with this question.

I sent him this to which he responded positively.

Sub Set_Two_Times()
Dim iSource As Long
Dim iTarget As Long
Dim cCols As Long
Dim rrows As Long
iSource = 1
iTarget = 1
cCols = InputBox("columns") '5
rrows = InputBox("rows per set") '42
Do
Cells(iSource, "A").Resize(rrows, cCols).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + rrows, "A").Resize(rrows, cCols).Cut _
Destination:=Cells(iTarget, (cCols + 1))
iSource = iSource + (rrows * 2)
iTarget = iTarget + (rrows)
PageBreak = xlPageBreakManual
Loop Until IsEmpty(Cells(iSource, "A").Value)
End Sub


Gord

On Tue, 27 Jan 2009 00:27:14 -0700, "Stanley Braverman"
wrote:

I am having a problem with the splitting order. If I use 50 rows per page
then the split and sorting order is ok. But if I want 42 rows per page then
the splitting order is out of proper sort.
Thanks


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Good to hear.

Thanks for the feedback

On Sun, 25 Jan 2009 10:28:10 -0700, "Stanley Braverman"
wrote:

Thank You. That worked very well.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Sub Move_Sets_PBreak()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 50, "A").Resize(50, 5).Cut _
Destination:=Cells(iTarget, "f")

iSource = iSource + 100
iTarget = iTarget + 50

PageBreak = xlPageBreakManual
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord Dibben MS Excel MVP

On Sun, 25 Jan 2009 09:10:36 -0700, "Stanley Braverman"
wrote:

I have a worksheet with over 300 pages that I need to print.
However the second page can be copied and pasted to the
first page to the adjacent columns and the page layout would work fine.

What I need is a macro to take page 2 and automatically
paste it to page 1 then delete the page 2 info and move up
the empty spaces from the worksheet and then continue
the process till all pages has been done.

In other words:
Page 1 plus page 2 now equals page 1
Then after moving up cleared cells
Page 2 and page 3 now equals page 2 (formally page 3 and 4)
Then after moving up cleared cells
Page 3 and page 4 now equals page 3 (formally page 5 and 6)etc..etc.etc

Involved are 4 columns for page 1 then 5 additional columns
for the new page to be added.5th column would be spacer

I can handle the spacing of the coulombs and rows and resizing the page
widths.

Thanks