How to create multiple files containing specific details from main file by VBA macro?

[greenbcz]

• Hi,
  
  I need to create different files (one per category ID) containing all details from main file using VBA macro. As you can see, attached main file has many rows and many tabs (containing different category ID). The macro when run should return many files (one per category ID) containing rows and tabs, for example:
  
  Category ID 256 one file with all related products and tabs
  Category ID 45 one file with all related products and tabs
  Categori ID ........... and so on.
  
  Looking forward for quick response. Thanks!
  
  
  
  

[mdmackillop]

Demo for first 2 products. Amend i to suit.

Option Explicit


Sub MakeProducts()
    Dim wb As Workbook
    Dim wbCopy As String
    Dim sh As Worksheet
    Dim sht As Worksheet
    Dim Lr As Long
    Dim Prod As Long
    Dim i As Long, j As Long
    
    Application.ScreenUpdating = False
    Set sh = Sheets("Products")
    For i = 2 To 3 'Sh.Cells(Rows.Count, 1).End(xlUp).Row
        Prod = sh.Cells(i, 1).Value
        wbCopy = ActiveWorkbook.Path & "\" & Prod & ".xlsm"
        ActiveWorkbook.SaveCopyAs wbCopy
        Set wb = Workbooks.Open(wbCopy)
        With wb
            For Each sht In .Sheets
                For j = sht.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
                    If sht.Cells(j, 1) <> Prod Then sht.Rows(j).Delete
                Next j
            Next sht
            Call DelCode(wb)
            wb.Close True
        End With
    Next i
    Application.ScreenUpdating = True
End Sub




Sub DelCode(wb)
Dim x
On Error Resume Next
    With wb.VBProject
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(x)
        Next x
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents(x).CodeModule.DeleteLines _
            1, .VBComponents(x).CodeModule.CountOfLines
        Next x
    End With
    On Error GoTo 0
End Sub

[Fennek]

Hello,

sorry, I don't understand anything:
* the ID in column "A" is just a sequential number
* it is a *.xlsx file, so no macros

Imaging there is an ID for a group, then a pivot-table with the ID as a filter would held: in the pivot-table-options each filter-value could send to a separate sheet. Then a very simple vba-code: for each sh in sheets: copy sh : next would create a file for each sheet/ID.

regards

(BTW: do you really work for Microsoft?)