Hi Jacob,

I used your code to copy data and a different sheet, however i am having a
little problem, and dont know how to modify the code, I have a large amount
of data maybe over 20K reconds, all sorted based on col Q, I want the macro
to create a sheet for each change in col Q and copy the data to the sheet and
name the sheet based on Col F. example,

Col A Col Q
John 1
John 1
John 1
John 1
Bob 2
Bob 2
Bob 2
Lisa 3
Lisa 3
Lisa 3
In this case the macro will create three sheets (John, Bob, Lisa) and copy
the data to each sheet.

Thanks in advance
David





"Jacob Skaria" wrote:

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)