Excel help

[mattvts23]

Evening all,

Im wanting to run a macro so that data in a sheet is split into new tabs. The data looks something like this:

Bill
Bill
Bill
Bob
Bob
Bob
Ben
Ben
Ben

I have about 15 names along with other data for each. I know it is possible to create a macros that opens new sheets within the same workbook. But can this be done ?????

Thanks Matt

[Liam_]

IIRC, Excel allows you to record custom macros. Go to Tools > Macro > Record New Macro, create your new tabs/ workbooks then click the stop recording button. With any luck, it should execute all the actions you performed during recording, including opening new sheets/ books etc.

[mattvts23]

But i would have to copy and paste each name into the new sheets. Would it not?

Thanks though.

[Liam_]

Managed to find this macro after some Googling.

Sub CopyPaste()

Dim LMainSheet As String
Dim LRow As Integer
Dim LContinue As Boolean

Dim LColAMaster As String
Dim LColATest As String

'Retrieve name of sheet that contains the data
LMainSheet = ActiveSheet.Name

'Initialize variables
LContinue = True
LRow = 2

'Start comparing with cell A2
LColAMaster = "A2"

'Loop through all column A values until a blank cell is found
While LContinue = True

LRow = LRow + 1
LColATest = "A" & CStr(LRow)

'Found a blank cell, do not continue
If Len(Range(LColATest).Value) = 0 Then
LContinue = False
End If

'Found occurrence that did not match, copy data to new sheet
If Range(LColAMaster).Value <> Range(LColATest).Value Then

'Copy headings
Range("A1:E1").Select
Selection.Copy

'Add new sheet and paste headings into new sheet
Sheets.Add.Name = Range(LColAMaster).Value
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 15
Columns("B:D").ColumnWidth = 20
Columns("E:E").ColumnWidth = 25
Range("A1").Select

'Copy data from columns A - Z
Sheets(LMainSheet).Select
Range(LColAMaster & ":E" & CStr(LRow - 1)).Select
Selection.Copy

'Paste results
Sheets(Range(LColAMaster).Value).Select
Range("A2").Select
ActiveSheet.Paste
Range("A1").Select

'Go back to Main sheet and continue where left off
Sheets(LMainSheet).Select
LColAMaster = "A" & CStr(LRow)

End If

Wend

Range("A1").Select
Application.CutCopyMode = False

MsgBox "Copy has completed."

End Sub

Just tried it in a Worksheet with the data in your original post and its placed the duplicate names in the same sheet. Shouldn't be a problem if your names are unique though. Also, that macro will only touch data that is in Columns A - E.

[mattvts23]

Do you know the code to put in VB which would add a column up on each sheet? Is this possible?

Thanks Matt