Hello,

I have a spreadsheet with multiple divisions that I need to split out into
separate tabs. I would like to do this with a macro if possible.

Example:

grp# division center
1 teamA 123
2 teamB 456
3 teamC 789

Thank you!

the following code sorts the data on Sheet1 using the division name. The
copies each Division to its own new worksheet.

Sub splitdata()

With Sheets("Sheet1")
'sort data to get divisions
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set DataRange = .Rows("2:" & LastRow)
DataRange.Sort _
key1:=.Range("B2"), _
Order1:=xlAscending, _
Header:=xlNo

First = 2
For RowCount = 2 To LastRow
If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then
Division = .Range("B" & RowCount)
Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))
newsht.Name = Division
'copy Header Row
.Rows(1).Copy Destination:=newsht.Rows(1)
.Rows(First & ":" & RowCount).Copy _
Destination:=newsht.Rows(2)
First = RowCount + 1
End If
Next RowCount
End With
End Sub


"Cathy Landry" wrote:

Hello,

I have a spreadsheet with multiple divisions that I need to split out into
separate tabs. I would like to do this with a macro if possible.

Example:

grp# division center
1 teamA 123
2 teamB 456
3 teamC 789

Thank you!

Hi Joel,

That worked like a charm!! Thank you so much :)

"Joel" wrote:

the following code sorts the data on Sheet1 using the division name. The
copies each Division to its own new worksheet.

Sub splitdata()

With Sheets("Sheet1")
'sort data to get divisions
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set DataRange = .Rows("2:" & LastRow)
DataRange.Sort _
key1:=.Range("B2"), _
Order1:=xlAscending, _
Header:=xlNo

First = 2
For RowCount = 2 To LastRow
If .Range("B" & RowCount) < .Range("B" & (RowCount + 1)) Then
Division = .Range("B" & RowCount)
Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))
newsht.Name = Division
'copy Header Row
.Rows(1).Copy Destination:=newsht.Rows(1)
.Rows(First & ":" & RowCount).Copy _
Destination:=newsht.Rows(2)
First = RowCount + 1
End If
Next RowCount
End With
End Sub


"Cathy Landry" wrote:

Hello,

I have a spreadsheet with multiple divisions that I need to split out into
separate tabs. I would like to do this with a macro if possible.

Example:

grp# division center
1 teamA 123
2 teamB 456
3 teamC 789

Thank you!