I have a long datasheet in Sheet 1. I would like to automatic put in a new
sheet in the workbook every time there is a new content in column D. If it is
possible to name the sheet after the new contents it would be great.

example:
Anonym 25.11.2008 08:25 Jente Gausdal vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3

When the content Gausdal change to Gjøvik i want a new sheet in the
woorkbook with the name Gjøvik conteining all data from column A to AC for
all rows with Gjøvik in column D.
I will be greatful for help

Sverre, try the below and feedback.

1. Column D should be sorted.
2. Column D should not have characters like "/|\" which are unacceptable
characters for sheetname.
3. Assume there are no sheetnames present with the text in ColD

Sub Macro()
Dim lngRef As Long
Dim lngRow As Long
Dim strRef As String
Dim lngLastRow As Long
Dim myWS1 As Worksheet
Dim myWS2 As Worksheet

Set myWS1 = ActiveSheet
lngLastRow = myWS1.Cells(Rows.Count, "D").End(xlUp).Row
For lngRow = 1 To lngLastRow
If strRef < "" And myWS1.Range("AC" & lngRow) < strRef Then
Set myWS2 = Sheets.Add(After:=myWS1)
myWS2.Name = myWS1.Range("D" & lngRow - 1)
myWS1.Range("A" & lngRef, "AC" & lngRow - 1).Copy myWS2.Range("A1")
strRef = Range("A" & lngRow)
lngRef = lngRow
End If
If strRef = "" Then strRef = myWS1.Range("AC" & lngRow): lngRef = lngRow
Next
Set myWS2 = Sheets.Add(After:=myWS1)
myWS2.Name = myWS1.Range("D" & lngRow - 1)
myWS1.Range("A" & lngRef, "AC" & lngRow - 1).Copy myWS2.Range("A1")

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Sverre" wrote:

I have a long datasheet in Sheet 1. I would like to automatic put in a new
sheet in the workbook every time there is a new content in column D. If it is
possible to name the sheet after the new contents it would be great.

example:
Anonym 25.11.2008 08:25 Jente Gausdal vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3

When the content Gausdal change to Gjøvik i want a new sheet in the
woorkbook with the name Gjøvik conteining all data from column A to AC for
all rows with Gjøvik in column D.
I will be greatful for help

Hi

I assume your data is sorted by column D before this macro is run.

Sub SplitData()
Dim off As Long
Dim FirstRow As Long
Dim StartCell As Range
Dim sh As Worksheet
Dim NewSh As Worksheet

FirstRow = 2 'Headings in row 1
Set sh = Worksheets("Sheet1")
Set StartCell = sh.Range("D" & FirstRow)
shName = StartCell.Value
Set NewSh = Worksheets.Add(after:=Sheets(Sheets.Count))
NewSh.Name = StartCell.Value
Do Until StartCell.Offset(off, 0) = ""
If shName = StartCell.Offset(off, 0) Then
sh.Range("A" & FirstRow + off, sh.Range("AC" & FirstRow + off)).Copy
_
Destination:=NewSh.Range("A2").Offset(NewOff, 0)
NewOff = NewOff + 1
off = off + 1
Else
Set NewSh = Worksheets.Add(after:=Sheets(Sheets.Count))
NewSh.Name = StartCell.Offset(off, 0).Value
sh.Range("A" & FirstRow + off, sh.Range("AC" & FirstRow + off)).Copy
_
Destination:=NewSh.Range("A2").Offset(NewOff, 0)
shName = NewSh.Name
NewOff = 0
off = off + 1
End If
Loop
End Sub

Best regards,
Per

"Sverre" skrev i meddelelsen
...
I have a long datasheet in Sheet 1. I would like to automatic put in a new
sheet in the workbook every time there is a new content in column D. If it
is
possible to name the sheet after the new contents it would be great.

example:
Anonym 25.11.2008 08:25 Jente Gausdal vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3

When the content Gausdal change to Gjøvik i want a new sheet in the
woorkbook with the name Gjøvik conteining all data from column A to AC for
all rows with Gjøvik in column D.
I will be greatful for help

See also
http://www.rondebruin.nl/copy5.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Sverre" wrote in message ...
I have a long datasheet in Sheet 1. I would like to automatic put in a new
sheet in the workbook every time there is a new content in column D. If it is
possible to name the sheet after the new contents it would be great.

example:
Anonym 25.11.2008 08:25 Jente Gausdal vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3

When the content Gausdal change to Gjøvik i want a new sheet in the
woorkbook with the name Gjøvik conteining all data from column A to AC for
all rows with Gjøvik in column D.
I will be greatful for help

Thank you Ron
This works. I am very grateful to you and all the other who helped me out.
Fabulous.
Nekt step for me is to automatic sending the sheet e-mail to the respetive
recipient.
Must I establish a work-book for easc sheet and send it, or is it possible
to send
only tre respective sheet direckt from this woorkbook ?
Regards
Sverre

See
http://www.rondebruin.nl/mail/folder2/row2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Sverre" wrote in message ...

Thank you Ron
This works. I am very grateful to you and all the other who helped me out.
Fabulous.
Nekt step for me is to automatic sending the sheet e-mail to the respetive
recipient.
Must I establish a work-book for easc sheet and send it, or is it possible
to send
only tre respective sheet direckt from this woorkbook ?
Regards
Sverre

Thank you Ron
This works. I am very grateful to you and all the other who helped me out.
Fabulous.
Nekt step for me is to automatic sending the sheet e-mail to the respetive
recipient.
Must I establish a work-book for easc sheet and send it, or is it possible
to send
only tre respective sheet direckt from this woorkbook ?
Regards
Sverre

Try this one.
Select datasheet(Sheet1?) and run the macro Copy_data2sheet below.
I presume your data start at row 2 and have header in row 1, but column
D don't need to be sorted. this macro refresh all data every time you
run the macro. a name of sheet with Gausdal is like Gausdal__1 to
distinguish data sheets from other sheets if any. if you want to do this
automatically, you have to put the event macro Worksheet_Change like
below into datasheet(Sheet1). if your data is large in size, this would
take time to complete.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 4 Then
Copy_data2sheet
End If
Application.EnableEvents = True
End Sub

Sub Copy_data2sheet()
Dim Srcsh As Worksheet, Dstsh As Worksheet, sh As Worksheet
Dim rng As Range, Dfilter As Range, Criterarng As Range
Dim strname As String

Application.ScreenUpdating = False

Set Srcsh = ActiveSheet
Set Dfilter = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
Dfilter.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set Criterarng = Dfilter.SpecialCells(xlCellTypeVisible)

For Each rng In Criterarng.Cells
strname = rng.Value
Srcsh.Range("A1").AutoFilter Columns("D").Column, strname
Set rng = Srcsh.AutoFilter.Range
Set rng = rng.Resize(, Columns("AC").Column)

On Error Resume Next
Set Dstsh = Nothing
Set Dstsh = Worksheets(strname & "__1")
If Dstsh Is Nothing Then
Set Dstsh = Worksheets.Add(After:=Worksheets(Sheets.Count))
Dstsh.Name = strname & "__1"
rng.Copy Destination:=Dstsh.Range("A1")
Else
Dstsh.Cells.ClearContents
rng.Copy Destination:=Dstsh.Range("A1")
End If
Next
Srcsh.Select
Srcsh.ShowAllData
Srcsh.AutoFilterMode = False

Application.DisplayAlerts = False
Worksheets(Srcsh.Range("D1").Value & "__1").Delete
For Each sh In Worksheets
If sh.Name Like "*__1" Then
Set rng = Nothing
Set rng = Criterarng.Find(Left(sh.Name, Len(sh.Name) - 3), _
LookIn:=xlValues, LookAt:=xlWhole)
If rng Is Nothing Then
sh.Delete
End If
End If
Next
End Sub

Keiji

Sverre wrote:
I have a long datasheet in Sheet 1. I would like to automatic put in a new
sheet in the workbook every time there is a new content in column D. If it is
possible to name the sheet after the new contents it would be great.

example:
Anonym 25.11.2008 08:25 Jente Gausdal vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3

When the content Gausdal change to Gjøvik i want a new sheet in the
woorkbook with the name Gjøvik conteining all data from column A to AC for
all rows with Gjøvik in column D.
I will be greatful for help

See also
http://www.rondebruin.nl/copy5.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Sverre" wrote in message ...
I have a long datasheet in Sheet 1. I would like to automatic put in a new
sheet in the workbook every time there is a new content in column D. If it is
possible to name the sheet after the new contents it would be great.

example:
Anonym 25.11.2008 08:25 Jente Gausdal vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3
Anonym 03.11.2008 13:49 Jente Gjøvik vg3

When the content Gausdal change to Gjøvik i want a new sheet in the
woorkbook with the name Gjøvik conteining all data from column A to AC for
all rows with Gjøvik in column D.
I will be greatful for help