Create 10 report from 1 report

[Krisz06]

Hi guys,
I would like to ask a little help.

I have a report about sales figure. The report contains a lot of sales data of all of the customers.
What I would need to do is to create a macro which will create the same report but instead of having all customer together, it would create a file for each customer separately.

What I was thinking that it would look through the Data sheet's Customer column and it would always chose a customer, then deletes all other customer, and then saving the file with the name of the customer.

Would you help me a little with the macro, how should i start it. I'm kind of stuck with it already at the beginning, how to create a loop to filter all separate customers.


Thank you for the help!!!

 

[igold]

A couple of questions:

-Where is the data. Does it start at Cell A2 (assuming row 1 contains headers), and what is the last column. what is the name of the worksheet that contains the all the information.
-What column contains the unique customer ID.
-When you say create a new file, are you saying a new worksheet or a new workbook.

If you could just share a little more information, it would make it easier to help you.

No matter how this turns out as far as the help you receive. If you decide write this macro on your own, the last thing I would do is delete data (just my two cents).

 

[Krisz06]

Hi igold, thank you for your answer, sorry for the secrecy :D

So, data is in "Data" tab, starting from cell C5, first row (row 5) is the header. The customer name (it is based on the name, as in total there are not that many customer it is sufficient with the name) is in column F (the 4th column of the table). As the data structure is like this, I would like to not change it to make it start from A1/A2 as other people would have problems with the structure change. And the last column is the AX column. I'm not sure about the last row, but 2000+, and the Data is formatted as table.
I mean a completely new workbook (xlsb file). So the plan is to download this one file from the network, and then use the macro to divide it to separate file for each customer.

The deletion of data is fine, as the original file is on network location, so that would never be deleted. And on the final files there should be data only for that exact customer who will receive the newly created file. The file is quite big, there are more tabs on it, but most of the tables, pivots, dashboards reading the data from the "Data" tab. So basically no other tab need to be touched, just the deletion of 'everybody else'. I'm not sure if there is any option to filter it for first unique name, and delete all not-visible cell, and do this in a loop for all unique name. I believe the final code would be something like this.

The unfortunate thing is that I don't have any rule on the names or IDs, so the macro should recognize how many different name is there and create a file for each.


Hope this information helps, and thank you very much for your help )

 

[igold]

What is the name of the main table? Does the customers info when placed in their workbook have to be formatted as a table as well, or will a range of data suffice. As an FYI, if the customers do not have some sort of unique identifier, you are headed for trouble in the future as your data grows.

 

[Krisz06]

The name of the table is tbData.
And a thing I forgot to mention.. this data from the database, where there is unique identifier, but this document has only a partial data extraction, there is no unique identifier. And I won't be able to change the extraction to have the unique identifier appearing in this document as well.

And i believe the table format should stay as there are multiple pivots and slicers connected to this table, so I think format changes could cause some errors with these other things in connection with the table.
That's why I thought the best way is to remove the data and leave the remaining part of the table untouched. Or maybe filter the data and copy it to a separate sheet and do some naming changes would be a better solution?

With the loop it can be problematic to do the second loop as in the first loop we deleted a lot of data?

 

[igold]

See if this gets you close to where you want to be...

This code should be pasted to a code module in your source workbook and run with the "Data" tab open...

Sub FilterTable()
    
    Dim Tb As Workbook: Set Tb = ThisWorkbook
    Dim tbl As ListObject: Set tbl = ActiveSheet.ListObjects("tbData")
    Dim arrCol As Variant
    Dim x As Long, n As Long
    
    Application.ScreenUpdating = False
    arrCol = tbl.ListColumns(4).Range
    With CreateObject("Scripting.Dictionary")
        For x = 2 To UBound(arrCol) - 1
            If Not IsMissing(arrCol(x, 1)) Then .Item(arrCol(x, 1)) = 1
        Next
        arrCol = .Keys
    End With
    For n = LBound(arrCol) To UBound(arrCol)
        tbl.Range.AutoFilter Field:=4, Criteria1:=arrCol(n)
        Dim nWb As Workbook: Set nWb = Workbooks.Add
        tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy ActiveSheet.Range("A2")
        tbl.HeaderRowRange.Copy ActiveSheet.Range("A1")
        ActiveSheet.Name = arrCol(n)
        ActiveWorkbook.SaveAs Filename:=arrCol(n) & ".xlsb", FileFormat:=50
        Tb.Activate
        tbl.Range.AutoFilter
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[Krisz06]

Hi igold,
wow, it works beautifully, to create a separate sheet with the data for each customers separately Bring me closer to the final destination.
Later I will analyse this code a little deeper to understand it better. Thank you! (I understand macros a little but this is too much for my capabilities)

But there are some other tricky things in the reports, I hope you can help me with that also. Now the data tab is saved only and no other tab is in the created files. Would that be possible to keep the other tabs in the documents as well? There are 7 more tab in it, some of them hidden (the background calculations).
I was looking around today on possible solutions and some seemed nice but there were nowhere that the original file, with all of it's worksheets are saved as well.
The problem that this file at the end should be run not by myself and should prepare exactly the same file (customer.xlsx name, all of the worksheets, and the filtered data under "Data" tab as a table called "tbData" to make the already existing pivots and other connections work in the same way)

So I'm not sure if all this is possible to achieve, it sounds too complicated for me :D But if you could help me with the other parts also, it would be awesome

 

[igold]

I am glad that so far it is working. I want to be sure that I understand your added requirement...

You would like all the other tabs except the "Data" tab to be copied over to the newly created workbooks. You also say that there are calculations that are being done with these other tabs.

To repeat and rephrase, you are saying that there are some calculations being performed with the additional seven tabs in all the newly created workbooks that are going to be looking for a worksheet named "Data" (the original customer sheet) that contains a table named "tbData".