Excel Sheet Data to Multiple Sheets

Question

I have an Excel sheet that has multiple rows of data. I'd like to be able to take each row and populate a new tab for each. For example, if I had a sheet that looked like this:

John Smith, 123 main st, BestTown, CA

Tom Jones, 567 1st street, worsttown, CA

Suzy Olsen, 876 2nd Street, MyTown, CA

I'd like to create three new tabs in my workbook, each populated with one person, i.e. John would have his own tab, Tom would have one and Suzy would have one.

Is there an easy way to do this?

Thanks.

Answer 1

Sorry, I forgot to add.

My sheet with the rows has about 300 rows, so I am looking for non-manual solution for this (i.e. I know I can do three rows/tabs manually, but not for 300).

Thanks.

Answer 2

Hi,

You may refer to my solution here - Split data into multiple tabs.

Hope this helps.

Answer 3

Try the below, it is fairly basic (no error checking), but may be a good place to start:

Option Explicit

Sub MakeIndivTabs()

'********************

'This creates a new spreadsheet tab for each name in a data list

'The code is dependent on the data list having a common structure:

' the structure must have the name followed by a comma (name,...)

  Dim ws As Worksheet

  Dim col As Collection

  Dim tabName As String

  Dim i As Long 'counter

  Dim countNames As Long

  Set ws = ThisWorkbook.Sheets("NameList")

  Set col = New Collection

  'count the number of data rows

  countNames = ws.Range("A" & Rows.Count).End(xlUp).Row - 1

  'cycle through each data row, extract the person's name

  'from the data and add the name to the collection;

  'using a collection ensures each name shows up only once

  'you should ensure your data list has no duplicates or

  'you will get an error

  For i = 1 To countNames

    tabName = Split(ws.Range("A" & (i + 1)), ",")(0)

    col.Add tabName, tabName

   'add a new tab with the individual's name

   'include an error check in case the name already exists

    Worksheets.Add.Name = col(i)

    Range("A1") = "OriginalData"

    Range("B1") = "Name"

    Range("C1") = "Other Columns"

    Range("A2") = ws.Range("A" & (i + 1))

    Range("B2") = tabName

    Range("C2") = "vlookup/index-match/etc as needed"

    ActiveSheet.Columns.AutoFit

  Next i

End Sub