Hi, I'm trying to split a workbook by the values in two different columns. I have a workbook that has letters in column J (A-R) and numbers in column K (1-10).
I can succesfully use the code here: http://www.mrexcel.com/forum/showthread.php?352057-Split-Worksheet-to-Multiple-Worksheets to split the workbook into seperate sheets named A-R. I could run the macro again and change the column to split on, but this has a few problems; It will only run on one worksheet, so I'd have to run it multiple times and, even if I modify the line:
to read:
it still doesn't name the new worksheets correctly.
So basically i want to try and do it in one hit. I know I'll need a two dimensional (recursive?) loop, with a 'j' counter as well as an 'i' counter but that's about as far as I've got. My VBA is pretty bad and I don't fully understand what happens in the loop.
It would also be helpful if it could modify the current cell to force upper case for the letter column because at the moment I have to select the column and run this first:
Which also modfies my header row.
Here's the code I'm currently using which I've modified to include a splitCol variable storing the column I wish to split by:
I've also had a look at this thread: http://www.mrexcel.com/forum/showthread.php?t=396069 which I might use to save them to different workbooks when I've solved this problem.
Any help you can give greatly appreciated! Ta.
I can succesfully use the code here: http://www.mrexcel.com/forum/showthread.php?352057-Split-Worksheet-to-Multiple-Worksheets to split the workbook into seperate sheets named A-R. I could run the macro again and change the column to split on, but this has a few problems; It will only run on one worksheet, so I'd have to run it multiple times and, even if I modify the line:
Code:
ws.Name = .Range(splitCol & iStart).Value
Code:
ws.Name = ws.Name & .Range(splitCol & iStart).Valueit still doesn't name the new worksheets correctly.
So basically i want to try and do it in one hit. I know I'll need a two dimensional (recursive?) loop, with a 'j' counter as well as an 'i' counter but that's about as far as I've got. My VBA is pretty bad and I don't fully understand what happens in the loop.
It would also be helpful if it could modify the current cell to force upper case for the letter column because at the moment I have to select the column and run this first:
Code:
'Convert Selection to Upper Case
Sub UpperCase()
Dim Cell As RangeFor Each Cell In Selection.Cells[INDENT]If Cell.HasFormula = False Then[/INDENT]
[INDENT=2]Cell = UCase(Cell)[/INDENT]
[INDENT]End If[/INDENT]
Next
End SubWhich also modfies my header row.
Here's the code I'm currently using which I've modified to include a splitCol variable storing the column I wish to split by:
Code:
'Split by Single Col
Sub SplitByCol()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Dim splitCol As String
Application.ScreenUpdating = False
With ActiveSheet
splitCol = "K"
lastrow = .Cells(Rows.Count, splitCol).End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range(splitCol & "2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom
iStart = 2
For i = 2 To lastrow
If .Range(splitCol & i).Value <> .Range(splitCol & i + 1).Value Then
iEnd = i
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = ActiveSheet
On Error Resume Next
ws.Name = .Range(splitCol & iStart).Value
On Error GoTo 0
ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
With ws.Rows(1)
.HorizontalAlignment = xlCenter
With .Font
.ColorIndex = 5
.Bold = True
End With
End With
.Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
iStart = iEnd + 1
End If
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End SubI've also had a look at this thread: http://www.mrexcel.com/forum/showthread.php?t=396069 which I might use to save them to different workbooks when I've solved this problem.
Any help you can give greatly appreciated! Ta.
Last edited:
