VBA - Create new sheet based on cell data

[rnanderson]

Is there a macro that will create new sheets based on a specific value in a column and copy that data to a new sheet?

For instance if I have a sheet that has 4 categories in one column. For example, Column A has A,B,C,D and associated data in the following columns. Is it possible to run a macro that creates a new sheet labeled A, B, C, D and pulls the associated data to those tabs.

Any help would be appreciated. Thank you.

 

[sheetspread]

http://www.mrexcel.com/forum/excel-...ulas-split-data-into-multiple-worksheets.html

 

[rnanderson]

That is not specifically what I am looking for. It is not copying the data to a new sheet.

 

[rnanderson]

To elaborate I have values in columns A-N. I would specifically like to pull data out based on values in column C. Say column C has three categories of A,B,C I would like the tabs to be created A, with all data associated with A pulled, and another tab B created with all data copied to that tab, and so on.. Is that possible??

 

[sheetspread]

It will work, be sure the sheet is named "Data", the column containing the split points "Group", and delete all other tabs

 

[rnanderson]

Sheetspread thank you for your help. It did end up working, but I chose to use the formula below. However, is there a way to make this code more efficient.

Sub Split()
Const NameCol = "E"
Const HeaderRow = 5
Const FirstRow = 6
Dim SrcSheet As Worksheet
Dim TrgSheet As Worksheet
Dim SrcRow As Long
Dim LastRow As Long
Dim TrgRow As Long
Dim Student As String
Application.ScreenUpdating = False
Set SrcSheet = ActiveSheet
LastRow = SrcSheet.Cells(SrcSheet.Rows.Count, NameCol).End(xlUp).Row
For SrcRow = FirstRow To LastRow
Student = SrcSheet.Cells(SrcRow, NameCol).Value
Set TrgSheet = Nothing
On Error Resume Next
Set TrgSheet = Worksheets(Student)
On Error GoTo 0
If TrgSheet Is Nothing Then
Set TrgSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
TrgSheet.name = Student
SrcSheet.Rows(HeaderRow).Copy Destination:=TrgSheet.Rows(HeaderRow)
End If
TrgRow = TrgSheet.Cells(TrgSheet.Rows.Count, NameCol).End(xlUp).Row + 1
SrcSheet.Rows(SrcRow).Copy Destination:=TrgSheet.Rows(TrgRow)
Next SrcRow
Application.ScreenUpdating = True
End Sub

 

[sheetspread]

It looks ok, maybe it could be shrunk but I don't think by much. You've tested it I presume?

 

[rnanderson]

Yes it is tested and it works appropriately. The issue is that I obtained this macro from somewhere else. My question is that it contains the word "Student" and I don't have that in my workbook. I am only wanting it to go through one column and pull the appropriate categories to their appropriate tabs. Sorry for my ignorance, but I do not know much about VBA. Any help would be appreciated.

 

[sheetspread]

Student is the unique row value/name assigned to each new sheet (A,B,C,D etc in your example above). You could change it to "Letter" or any other category.