Sub Separate()
Application.ScreenUpdating = False
Dim i As Integer
Dim rng As String, rng2 As String
Dim MyStart As String
MyStart = ActiveCell.Address
Dim Sht As Worksheet
Set Sht = ActiveSheet
Sheets.Add.Name = "TempForm"
Sht.Range("A:K").Copy
Sheets("TempForm").Range("A1").PasteSpecial
Range("J1").EntireColumn.Copy
Range("I1").EntireColumn.Insert
Range("L1").EntireColumn.Copy
Range("I1").EntireColumn.Insert
Range("L:M").EntireColumn.Delete
Range("K:K").Select
Selection.TextToColumns Destination:=Range("K1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:=
_
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Dim rLast As Integer
rLast = ActiveSheet.UsedRange.Rows.Count
Range("L" & rLast).Select
Do
If IsEmpty(ActiveCell) Then
ActiveCell.Offset(-1, 0).Activate
Else
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.Copy
rng = ActiveCell.Address
ActiveCell.Offset(1, -1).PasteSpecial
Range(rng).Select
Selection.Delete
Range(ActiveCell.Offset(0, -11).Address, ActiveCell.Offset(0,
-3).Address).Copy
ActiveCell.Offset(1, -11).PasteSpecial
Range(rng).Select
End If
Loop Until ActiveCell.Address = "$L$1"
Range("I:I").Copy
Range("L1").PasteSpecial
Range("J:J").Copy
Range("L1").EntireColumn.Insert
Range("I:J").EntireColumn.Delete
Range("A:K").Copy
Sheets(Sht.Name).Activate
Range("A1").Select
Selection.PasteSpecial xlValues
Application.DisplayAlerts = False
Sheets("TempForm").Delete
Application.DisplayAlerts = True
Range(MyStart).Select
Application.ScreenUpdating = True
End Sub
JokerFrowns Wrote:
Seems to be working great except for one minor issue which I think can
be solved by an integer count possibly... the datatable starting as:
for example

A B C D E F G H I
J
K
1 2 3 4 5 6 7 8 9i, 9ii, 9iii
10 11
a b c d e f g h ii,iii,iiii
j k

is being split in the following manner...

A B C D E F G H I J K
1 2 3 4 5 6 7 8 9i 10
11
1 2 3 4 5 6 7 8 9ii j
11
1 2 3 4 5 6 7 8 9iii
11
a b c d e f g h ii
k
a b c d e f g h iii
k
a b c d e f g h iiii
k

When infact what I need it to be doing is...

A B C D E F G H I J K
1 2 3 4 5 6 7 8 9i 10
11
1 2 3 4 5 6 7 8 9ii
11
1 2 3 4 5 6 7 8 9iii
11
a b c d e f g h ii j
k
a b c d e f g h iii
k
a b c d e f g h iiii
k

Is it possible to modify the code you just gave me to allow for this
type of split? Otherwise it seems to be working exactly as needed.

Once again, many many thanks for all the help.


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=542520