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" <(E-Mail Removed)> skrev i meddelelsen
news:5FE49F34-BC86-49CB-A67F-(E-Mail Removed)...
>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 Jacob.

Debugging:
The program stopps at this statement:
myWS2.Name = myWS1.Range("D" & lngRow - 1)

Any sheets were created? Try with the below to know at which sheetname the
error is populated.

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

On Error GoTo ErrHandler

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")
myWS1.Activate
ErrHandler:
MsgBox "Sheet name:" & Range("D" & lngRow - 1),,"Row:" & lngRow
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"Sverre" wrote:

>
> Thank you Jacob.
>
> Debugging:
> The program stopps at this statement:
> myWS2.Name = myWS1.Range("D" & lngRow - 1)
>
>
>

My data is sorted by column D

Than you very much. I have tryed this one as well. I got a massage:
Syntax error in this statment:

_
Destination:=NewSh.Range("A2").Offset(NewOff, 0)
Destination:=NewSh.Range("A2").Offset(NewOff, 0)

Hi
The problem is word wrap in you news reader.

the underscore sign "_" should be last charachter on the line above. Like
this:

sh.Range("A" & FirstRow + off, sh.Range("AC" & FirstRow + off)).Copy _
Destination:=NewSh.Range("A2").Offset(NewOff, 0)

Hopes this helps.

Per

"Sverre" <(E-Mail Removed)> skrev i meddelelsen
news1462EE2-D76D-4556-8773-(E-Mail Removed)...
>
> My data is sorted by column D
>
> Than you very much. I have tryed this one as well. I got a massage:
> Syntax error in this statment:
>
> _
> Destination:=NewSh.Range("A2").Offset(NewOff, 0)
> Destination:=NewSh.Range("A2").Offset(NewOff, 0)
>
>
>

A dialoxbox ask for sheetname sheet 2.

One sheet was put in. The name was Ark1 or in english sheet 4. I deletet the
first sheet 2. After that it look like the excel put in sheet 3 despite I
have deletet the
sheet 2 after the stop in the program.

I tryed it,but it dosent help.

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

Have I changed it right ?

In a new workbook with one sheet; try with some dummy data.

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


"Sverre" wrote:

>
> A dialoxbox ask for sheetname sheet 2.
>
> One sheet was put in. The name was Ark1 or in english sheet 4. I deletet the
> first sheet 2. After that it look like the excel put in sheet 3 despite I
> have deletet the
> sheet 2 after the stop in the program.