Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
insert page breaks every 43 lines
the code below works to transpose data to a sheet called "All".
however, i've tried several ways of inserting pagebreaks for every 43 rows of transposed data on sheet "All". any suggestions? thanks, chase Public Sub TransposeToOneColumn() Dim sourceSht As Worksheet Dim destSht As Worksheet Dim destRow As Long Dim cell As Range Dim Counter As Integer Worksheets("All").ResetAllPageBreaks Application.ScreenUpdating = False Set sourceSht = Worksheets("Projects") Set destSht = Worksheets("All") destRow = 1 For Each cell In sourceSht.Range("A4:A" & Range("A" & _ Rows.Count).End(xlUp).Row) cell.Resize(, 43).Copy destSht.Range("B" & destRow).PasteSpecial Transpose:=True sourceSht.Range("A2:AQ2").Copy destSht.Range("A" & destRow).PasteSpecial Transpose:=True destRow = destRow + 43 Next cell Sheets("All").Activate Columns("B:B").Select Selection.ColumnWidth = 55 With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlTop .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("A:A").Select Selection.ColumnWidth = 32 With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlTop .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rows(44).PageBreak = xlManual Application.CutCopyMode = False Application.ScreenUpdating = True End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: how to insert page breaks between all the rows at once? | Excel Discussion (Misc queries) | |||
How do I insert page breaks in .txt for use in excel? | Excel Discussion (Misc queries) | |||
Insert Multiple Page Breaks | Excel Worksheet Functions | |||
In Excel, how can I keep 2 lines together when page breaks to 2nd | Excel Discussion (Misc queries) | |||
Can I set an automatic page breaks to break every 40 lines? | Excel Discussion (Misc queries) |