Hi Mike,
Thanks for your quick reply.i am geting an error message" invalid proceedure
call or argument'.
Sutha

"Mike H" wrote:

Hi,

You didn't say where you want the data pasting so this pastes ot ti row 1 in
the addded sheet. Alt+F11 to open VB editor. Right click 'This workbook' and
insert module and paste this code in and run it. It assumes your P&L data are
on sheet 1

Sub Liminal()
lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In myrange
ShName = Left(c.Value, InStr(c.Value, " ") - 1)
Set c = c.Resize(, 20)
c.Copy
Worksheets.Add After:=ActiveSheet
ActiveSheet.Name = ShName
Range("A1").PasteSpecial
Next
End Sub

Mike

"sutha" wrote:

I HAVE A SHEET WITH P&L FOR 100 COMPANIES ONE BELOW ANOTHER. EACH P&L IS SAME
IN SIZE( 20 RAWS)

Can someone please help with a sub that will copy each p&l in different
sheet and name each sheet with the 1st word found in the begining of the
range (Eg; raw 1
company name "xxx")