Page1 to Page2

[Stanley Braverman]

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

 

[Gord Dibben]

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

 

[Stanley Braverman]

Thank You. That worked very well.

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

 

[Gord Dibben]

Good to hear.

Thanks for the feedback

Thank You. That worked very well.

 

[Stanley Braverman]

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]

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