formatting to fit on one page

fedds2

New Member
Joined
Dec 30, 2005
Messages
32
If you have 4 rows of data and say 60 columns, how can you write a macro to find the edge of page and move the data nominally on pages 2 and 3 to appear on page 1. In other words rows 1 to 4 have page 1 data, rows 9-13 have page 2 data and rows 17-21 have page 3 data.
Clear as mud :biggrin: ?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
Just so you know, this code runs really slowly. I think the "vpagebreaks" property is really inefficient, although I don't know why.

Please note the workaround with selecting the last cell on the sheet, the vpagebreaks property often fails without it. see the URL referenced for details.

Rich (BB code):
Sub MoveData()
    Dim breaks As Integer, i As Integer
    Dim col1 As Integer, col2 As Integer
    Dim rngCut As Range
    Dim pasterow As Integer
    
    breaks = ActiveSheet.VPageBreaks.Count
    
    pasterow = 9
    Range("IV65536").Select ' this is a workaround for buggy behavior, see http://support.microsoft.com/default.aspx?scid=kb;en-us;210663
    For i = 1 To breaks
        If i < breaks Then
            col1 = ActiveSheet.VPageBreaks(i).Location.Column
            col2 = ActiveSheet.VPageBreaks(i + 1).Location.Column - 1
        Else
            col1 = ActiveSheet.VPageBreaks(i).Location.Column
            col2 = Range("IV1").End(xlToLeft).Column
        End If
        Set rngCut = Range(Cells(1, col1), Cells(4, col2))
        rngCut.Cut Range("A" & pasterow)
        pasterow = pasterow + 8
    Next i
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,290
Messages
5,571,327
Members
412,382
Latest member
Langtn02
Top