On Apr 27, 10:27*pm, Valerie
wrote:
Hello, all.

I have a spreadsheet that has "sections" - a header row and several detail
rows beneath it until the next header row, etc. *The sections are different
companies. This is for an JE upload into SAP and the row count for each
company section is limited to 190 rows. *I have 2 companies that are always
larger than 190 and occasionally one other company. *This is the macro I
currently have that is for a specific company:

'Split lines longer than 190 for US14

* *''' where to search
* *Set rg = ActiveSheet.Range("E:E")
* *''' search for 'found'
* *Set rg = rg.Find(What:="US14", LookIn:=xlValues, LookAt:=xlWhole)
* *''' process result
* *If rg Is Nothing Then * ''' was not found
* * * MsgBox "Not found"
* *Else
* * * ''' go 190 rows below that found cell
* * * Set rg = rg.Offset(190)
* * * ''' resize to 2 rows
* * * Set rg = rg.Resize(2)
* * * ''' insert 2 rows
* * * rg.EntireRow.Insert xlShiftDown
* *End If
* * 'Range("A1").Select
* * Selection.End(xlUp).Select
* * ActiveCell.Offset(0, -4).Select
* * Selection.EntireRow.Copy
* * Selection.End(xlDown).Select
* * ActiveCell.Offset(2, 0).Select
* * ActiveSheet.Paste
* * 'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
* * * * False, Transpose:=False
* * Application.CutCopyMode = False

* * Cells.Find(What:="US14 Total", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
* * * * xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
* * * * False).Activate
* * Selection.EntireRow.Copy
* * Selection.End(xlUp).Select
* * ActiveCell.Offset(-1, 0).Select
* * ActiveSheet.Paste
* * Application.CutCopyMode = False
* * ActiveCell.Offset(0, 8).Select
* * ActiveCell.FormulaR1C1 = _

"=SUMIF(R[-189]C3:R[-1]C3,40,R[-189]C9:R[-1]C9)-SUMIF(R[-189]C3:R[-1]C3,50,*R[-189]C9:R[-1]C9)"

I am wondering if there is a way this macro could be enhanced/revised to
where XL would evaluate the number of lines within the company section to see
if a break is needed and if so, how many breaks (US14 often needs 2 - has
more than 380 lines) and insert these breaks. *I currently have to do the
second break manually. *Any help with this would be greatly appreciated!!

Thanks!
Valerie

I imagine I would search for the header rows and get the number of
rows inbetween.
If more than "190" then insert accordingly, else do nothing.