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

 

[SkipVought]

Hi,

I have to reiterate that chopping data up into various sheets is really a bad tactic. Filters can easily be employed to isolate data in a table.

If you insist on a separate sheet, why not consider a Data/Validation Drop-Down (Customers) that would trigger a query based on the drop-down selection. Then you have only one extra sheet to maintain in your workbook, remembering that each addition sheet in your workbook has a marginal maintenance overhead.

Sample attached

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

 

[Cpreston]

Hi

Thanks for the reply and also thanks for the example. It says it is read only if I try and change the customer ID in the example you sent. I also cannot work out how it all fits together.

I tried recreating it in a new sheet but could no get the information into the customer tab from the source sheet. So a little stuck.

Thanks

 

[SkipVought]

Did you Enable Macros?
Did you Enable Content?

I just downloaded and opened and operated the workbook from Tek-Tips.

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

 

[Andrzejek]

Skip, of course it works for you

Sub tt_NewTabs()
    Dim sPath As String, sConn As String, sDB As String, sSQL As String, sSht As String
    
    sPath = ThisWorkbook.Path
    sDB = ThisWorkbook.Name
    sSht = ActiveSheet.Name

    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "." & sDB & ";"
    sConn = sConn & "DefaultDir=[red]C:\Users\Skip\Documents[/red];"
    sConn = sConn & "DriverId=1046;MaxBufferSize=2048"

    sSQL = sSQL & "SELECT"
...

I modified my code to say:

    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "." & sDB & ";"
    sConn = sConn & "DefaultDir=[blue]" & sPath & ";"[/blue]
    sConn = sConn & "DriverId=1046;MaxBufferSize=2048"

And it started to work, but if you click on other worksheets and come back to Query worksheet and select from cell A1, you get the message: "Cannot update Database or object is read-only" and asks to "Select Workbook" showing the dialog box.

---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

yes it appears to be trying to open another file from somewhere.

 

[SkipVought]

Sorry. I failed to complete my connection string code.

New file attached

Thanx, Andy.

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

 

[SkipVought]

Download my latest file now. See previous post above.

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

 

[Cpreston]

Hi

Ok, thanks, I had to create the path for the file to work but is working now. I will see how it all fits together and try and set my spreadsheet like that.

Thanks again

 

[SkipVought]

Great. I’m sure you’ll have questions along the way. Post a new thread for each specific question when they come up.

I had to create the path for the file to work

If you download the latest, it would be better. That way, if you move your workbook, it will continue to run.

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

 

[SkipVought]

@Andy, thanx for pointing that out. Going senile (but not viewing any rivers).

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

 

[Cpreston]

Hi Skip

This happened with the latest I downloaded, it was looking for a path. How do you get into the code?

Thanks

 

[Andrzejek]

Any time

Additional question about your workbook: when you click on other sheets, and come back to Query, it still asks to select workbook :-(

Also, if you filter Customer d on column C, the filter 'stays' and if you select another Customer, this filter still applies and no records are shown. Is there a way to eliminate filters when new Customer is selected so to start 'fresh'?


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Andy, The query on sheet CustList was a quickie, one timer, to get a distinct list for this demo. If this was a production workrbook, it, too, would have path and workbook name correction code.

Filters? There are no filters in this process. All the action happens on the Query sheet. Everything else is reference.

FOR THE OP:
The essential parts of this approch are:

1) a sheet containing a source data table
2) a sheet containing an MS Query returning a unique list of Customers for the purpose of popuating a Date > Validation list.
3) a sheet containing a) a Data > Validation list for selecting a customer and b) an MS Query that returns data from the source data for the selected customer. That query will be a PARAMETER query, referencing the selected customer and when that selected customer cell chsnges value, the query will fire.

The devil is in the details

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

 

[SkipVought]

This happened with the latest I downloaded, it was looking for a path. How do you get into the code?

The code is in the ThisWorkbook module. It was a quickie demo.

Use it as an example. Add a standard Module in your workbook. Start from scratch adding QueryTables via Data > Get external data > From other sources > From Microsoft Query....

See my outline above.


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

 

[Andrzejek]

Skip,

Filters:
[ul]
[li]Customer d, 5 records (row 3 to 7)[/li]
[li]Customer a, 2 records (row 3 and 4)[/li]
[li]back to Customer d with 5 records[/li]
[li]In column F, use the drop-down arrow to Filter the records, check [tt]a610[/tt], 1 record[/li]
[li]Customer a, no records now :-( You have to know to select the arrow in Column F to Select All to have the 2 records back.[/li]
[/ul]

Sorry to be a PITA, but I like your approach and hope to use it in my world...


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

My approch uses no filters.

We can have a discussion of filters along with some automation, but that’s not part of THIS discussion.

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

 

[Andrzejek]

Fair enough


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Start a thread. Post working example. State the desired behavior.

I think that would be a profitable discussion

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

 

[Cpreston]

Hi

Struggling with this way unfortunately. The customer column could have over 1000 entry's with duplicate customer names, each time I refresh this could potentially change.

Therefore firstly how do I create the customer tab that picks up the source A column and then only shows one customer name only and not duplicate. Think I need to do a step by step process on this.

Thanks