Split main data sheet in other sheets based on customer name 1

[Cpreston]

Hi

I have a list of customers in a data sheet with 5 other columns of data relating to the customer.

I want to do the following from the data sheet.

In the same workbook create a new tab from the customer name and also copy in all the other column information relating to that customer.

So if I have 5 different customer names in the main data sheet, I will have 5 new tabs with their names on the tabs and also the information relating to that customer in the named sheet.

I have found a lot of code where I can name the tabs, some work ok, but none where it takes the related columns and rows also.

Any help please. Thanks in advance

 

[Cpreston]

A little bit more forward.

I now have a sheet with the tabs Source, Query and Custlist

In the custlist sheet I have a unique list of customers from the Source sheet.

I have made the custlist into a table called Tcust

I have gone to the Query sheet and tried in column A1 to give it a data validation of List and source of =Custlist

When I click OK on this I get a message saying a Named Range you have specified cannot be found. So stuck here at the moment. Any ideas please.

Thanks

 

[Cpreston]

Sorry about this but another update.

I now have the data validation working and in the query sheet have it in A1 and showing the customer name in the drop down list.

I now cannot figure out how to create a query that will work, how do I query to the sheet I am using, being it all the same one?

Thanks

 

[Cpreston]

Final one, have it all working now. Thanks for the reply's last week and good example to work from.

 

[SkipVought]

Just got back to view your posts today. Glad that you've worked out each of those issues.

I'd strongly suggest something that I did with nearly all of my MS Queries in Excel.
[tt]
Turn on your macro recorder.
EDIT the query (Right-Click > Table > Edit Query... > File > Return data to Microsoft Excel
Turn off your recorder
[/tt]

Here's my macro record result while editing the CustList...

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A3").Select
    With Selection.ListObject.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=C:\Users\Skip\Documents\tt-NewTabs.xlsm;DefaultDir=C:\Users\Skip\Documents;DriverId=1046;MaxBufferSize=2048" _
        ), Array(";PageTimeout=5;"))
        .CommandText = Array( _
        "SELECT DISTINCT `Sheet1$`.Customer" & Chr(13) & "" & Chr(10) & "FROM `Source$` `Sheet1$`")
        .Refresh BackgroundQuery:=False
    End With
End Sub

What I do next is something that I've done and will continue to do, changing the above to what is below...

Sub qCustList()
'SkipVought 2018 AUG 7 (a day that will live in infamy)
'
    Dim sPath As String, sDB As String, sSRC As String
    Dim sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path   'path for this workbook
    
    sDB = ThisWorkbook.Name     'name of this workbook
    
    sSRC = wsSource.Name       'wsSource is the (name) or CodeName
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=1046;MaxBufferSize=2048"

    sSQL = "SELECT DISTINCT"
    sSQL = sSQL & " a.Customer"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `'" & sSRC & "$'` a"

    ThisWorkbook.Save
    
    With wsSource.ListObjects(1).QueryTable
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

This code needs to execute anytime the Source sheet changes. So I'd execute qCustList in the Worksheet_Deactivate event for sheet Source.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]