Extracting date

dlucas980 

If you are open to a VBA solution, you may try something like this...

Sub SplitDataToSheets()
Dim wsData      As Worksheet
Dim dws         As Worksheet
Dim shName      As String
Dim lr          As Long
Dim lc          As Long
Dim Rng         As Range
Dim str         As Variant
Dim acRng       As Range
Dim dtRng       As Range

Application.ScreenUpdating = False

Set wsData = Worksheets("Master")

lr = wsData.Cells(Rows.Count, 1).End(xlUp).Row
lc = wsData.Cells(2, Columns.Count).End(xlToLeft).Column

For Each Rng In wsData.Rows(2).SpecialCells(xlCellTypeConstants, 2).Areas
    str = Split(Rng.Value, Chr(10))
    shName = str(0) & " Account"
    Set acRng = wsData.Range(wsData.Cells(Rng.Row + 1, 1), wsData.Cells(lr, 1))
    Set dtRng = wsData.Range(wsData.Cells(Rng.Row + 1, Rng.Column), wsData.Cells(lr, Rng.Column + 1))
    
    On Error Resume Next
    Set dws = Worksheets(shName)
    dws.Cells.Clear
    On Error GoTo 0
    
    If dws Is Nothing Then
        Set dws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
        dws.Name = shName
    End If
    
    dws.Range("B2").Value = Rng.Value
    acRng.Copy dws.Range("B3")
    dtRng.Copy dws.Range("C3")
    dws.Range("A3").Value = "#"
    dws.UsedRange.RowHeight = 15
    
    With dws.Range("A4:A" & lr)
        .Formula = "=Row()-3"
        .Value = .Value
    End With
    
    With dws.Range("B2:D2")
        .Merge
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .RowHeight = 50
    End With
    dws.UsedRange.Columns.AutoFit
    
    Set dws = Nothing
Next Rng

wsData.Select
Application.ScreenUpdating = True
End Sub

In the attached, click the button called "Split Accounts" on Master Sheet.

dlucas980 

First off, storing data records in rows is the far more common way, and entirely suited to Pivot Tables. And then you can convert the range of data to an Excel Table and far more easily do other things as well--e.g., take advantage of the newer Dynamic Array functions SORT, UNIQUE, FILTER...

So let me first disabuse you of the notion that having your client account information in columns is suitable. They may work for Pivot Tables, because of the flexibility of Pivot Tables, but they'll end up limiting you more than helping you. Seriously. Check out any texts on data bases and tables in Excel; I doubt you'll find a one that advocates doing the records as columns.

Second, could you say more about what you'll be storing in those individual sheets, one per client, and maybe a word or two about how many clients you're talking about.

It's clearly a database of some kind that you're  building. The question is what would  be the best database design to accomplish your purposes. Excel is really VERY good at taking a single table and producing a summary report. The Pivot Table, with its cross-tabulated summary of monthly transactions per client (for example) is something you're already familiar with. It's also possible that you'd be better off using a single table for all transactions, keyed (obviously) to each client's account number(s), but still a single table for all of them, and then let Excel do it's trick of separating out the relevant transactions (or whatever) per client......i.e., let Excel do the "heavy lifting" of extracting data based on a few parameters, rather than having to search for each client's separate sheet every time you want to enter something pertinent.

I'm not saying that's a foregone conclusion, but I do want to make sure you've considered the possibility rather than assuming that, because you'll want OUTPUT that is client specific, that means you also need to have the INPUT be on client specific sheets. That premise is not a necessary (or, for that matter, helpful) way to approach your design.

So, if you can forgive the impertinence of my asking you to explain your business goals here rather than just helping you do what you asked, my goal (and I think that of others on this site, many of whom are far more expert with such things as Power Query than I) is to help you come up with the best design, and to do that we need to ask for a description of the bigger context within which you're operating.