PDA

View Full Version : How to create multiple files containing specific details from main file by VBA macro?



greenbcz
06-23-2017, 04:44 PM
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
06-24-2017, 01:46 AM
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
06-25-2017, 06:57 AM
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?)