On Dec 25, 3:24*pm, joel wrote:
Here is the updates. *I added a page break after each page.
Sub FormatData()
Set SourceSht = Sheets(1)
FirstPageRow = 2
LastPageRow = 58
NewRowCount = FirstPageRow
NewColCount = 3
With SourceSht
With .Cells.Font
Name = "Arial"
FontStyle = "Regular"
Size = 10
End With
'If data start i A1 then add new row
If .Range("A1") < "Inst. No." Then
Rows(1).Insert
Range("A1") = "Inst. No."
Range("B1") = "Memo"
End If
Range("C1") = "Inst. No."
Range("D1") = "Memo"
RowCount = 59
Do While .Range("A" & RowCount) < ""
If NewRowCount LastPageRow Then
NewColCount = NewColCount + 2
'create new sheet
If NewColCount 8 Then
'add page break
ActiveWindow.SelectedSheets.HPageBreaks.Add _
Befo=.Range("A" & (LastPageRow + 1))
NewColCount = 1
FirstPageRow = FirstPageRow + 58
LastPageRow = LastPageRow + 58
End If
Cells(FirstPageRow - 1, NewColCount) = "Inst. No."
Cells(FirstPageRow - 1, NewColCount + 1) = "Memo"
NewRowCount = FirstPageRow
End If
Cells(NewRowCount, NewColCount) = _
Range("A" & RowCount)
NewRowCount = NewRowCount + 1
RowCount = RowCount + 1
Loop
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If NewColCount = 1 Then
Rows(NewRowCount & ":" & LastRow).Delete
Else
Rows((LastPageRow + 1) & ":" & LastRow).Delete
End If
End With
End Sub
--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=165111
Microsoft Office Help
Hey joel,
Thanks for doing an amazing job here. Just two more small things..
I ran into this same issue last time when I developed the original
code, by the way... I couldn't seem to get data from column A from
being duplicated in column B. This time, we've managed to get it
mainly fixed, but there is still one cell that duplicates its data.
- The last entry on the first page of column A (cell A58, that is),
appears duplicated into cell C2. That only happens on the first page.
- The font and the font size doesn't seem to change to Arial 10. I'm
working with raw data in Calibri 11 font, when I activate the macro.
Any idea what we could possibly do on those two things? I've been at
it for a while trying some things, but no luck so far.
This is really excellent otherwise!
randy