VBA Macro for creating multiple workbooks using a template form with data sourced from one worksheet

Question

Hello!

I am trying to create a VBA macro to create multiple workbooks from one data set. We would want to later send out the individualized workbooks to different customers, so we only want their data on their personal workbook, not that of other customers.

Our data source would look like this:

Supplier#	Supplier Name	Our Part Number	Part Description
123456	Best Company	3132168	Screw
123456	Best Company	L456	Nut
123456	Best Company	G586	Bolt
123456	Best Company	M56	Wrench
456789	Sun Company	D659	Wrap
456789	Sun Company	E569	Liner
456789	Sun Company	W569	Cover
987456	Ground Express	L569	Box
987456	Ground Express	L5645	Carton

And I would like a workbook created for each supplier using this template to put all of their parts on it: (so copying the data into the first four columns)

Responses Required
		Supplier: Please complete all of the grey colored cells in Columns E through Y
Purchasing & Imported Goods Data
Our Part Number	Part Description	Supplier #	Supplier Name	Vendor Part Number	Country of Origin	CN Code	CN Code Description	Date of Data Submission

So, Best Company would have one workbook with all of their parts on it. Sun Company would have a workbook of their parts and so on.

My knowledge of VBA macro is wayyyyy to limited for this. Can anyone help?

Answer 1

Hi WorkerBeeEl,

Welcome to the Microsoft community.

Certainly, here's a VBA macro that may achieves what you're looking for. This macro will iterate through your data, identify unique suppliers, and create a new workbook for each supplier using a predefined template. It will then copy the relevant data for each supplier into their respective workbooks.

Assuming your data starts from cell A2 on "Sheet1" :

and the template worksheet is named "Template":

1. Open Excel and press Alt + F11 to launch the VBA Editor.
2. Within the VBA Editor, ensure the VBAProject (YourWorkbookName) is selected on the left, with YourWorkbookName being your workbook's name. Right-click on this workbook title and choose Insert > Module from the context menu to create a new module.
3. Remember to enable the Microsoft Scripting Runtime library if prompted (Tools > References > Check Microsoft Scripting Runtime) to use the Dictionary object.
4. In the new module's code window, input the following VBA code: Sub CreateWorkbooksForSuppliers()

    Dim wsData As Worksheet 
   
    Dim wsTemplate As Worksheet 
   
    Dim lastRow As Long 
   
    Dim uniqueSuppliers As Object 
   
    Dim supplier As Variant 
   
    Dim newBook As Workbook 
   
    Dim i As Long, j As Long 
   
    ' Set references to data sheet and template 
   
    Set wsData = ThisWorkbook.Sheets("Sheet1") 
   
    Set wsTemplate = ThisWorkbook.Sheets("Template") 
   
    ' Turn off screen updating for speed 
   
    Application.ScreenUpdating = False 
   
    ' Find last row of data 
   
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row 
   
    ' Create a dictionary to store unique suppliers 
   
    Set uniqueSuppliers = CreateObject("Scripting.Dictionary") 
   
    ' Identify unique suppliers 
   
    For i = 2 To lastRow 
   
        If Not uniqueSuppliers.exists(wsData.Cells(i, 1).Value) Then 
   
            uniqueSuppliers.Add wsData.Cells(i, 1).Value, Nothing 
   
        End If 
   
    Next i 
   
    ' Loop through each unique supplier 
   
    For Each supplier In uniqueSuppliers.keys 
   
        ' Create a new workbook for the supplier 
   
        Set newBook = Workbooks.Add(xlWBATWorksheet) 
   
        ' Copy template sheet to the new workbook 
   
        wsTemplate.Copy Before:=newBook.Sheets(1) 
   
        newBook.Sheets(1).Name = "Supplier Data" 
   
        ' Find the next available row in the new workbook 
   
        j = newBook.Sheets("Supplier Data").Cells(newBook.Sheets("Supplier Data").Rows.Count, "A").End(xlUp).Row + 1 
   
        ' Copy data for the current supplier to the new workbook 
   
        For i = 2 To lastRow 
   
            If wsData.Cells(i, 1).Value = supplier Then 
   
                wsData.Rows(i).Copy Destination:=newBook.Sheets("Supplier Data").Rows(j) 
   
                j = j + 1 
   
            End If 
   
        Next i 
   
        ' Save and close the new workbook 
   
        newBook.SaveAs Filename:="Supplier_" & supplier & ".xlsx", FileFormat:=xlOpenXMLWorkbook 
   
        newBook.Close SaveChanges:=False 
   
    Next supplier 
   
    ' Notify user and turn screen updating back on 
   
    MsgBox "Workbooks have been created for each supplier.", vbInformation, "Completed" 
   
    Application.ScreenUpdating = True 
   

   End Sub

Before running this macro, ensure that you have a worksheet named "Sheet1" containing your data and another named "Template" set up as you desire for the output format. This macro creates new workbooks based on the unique supplier numbers, naming them accordingly (e.g., "Supplier_123456.xlsx"), and saves each workbook in the same directory as your original workbook.

Here is an example:

Please note that the code as it stands doesn't directly fulfill your specified requirement, as it lacks the particular data types from the individual supplier sheets you intend to categorize. After running this code, you would need to proceed with populating your desired format using the data from each supplier's respective sheet.

Should you have any questions regarding the execution of this code or require further assistance, please don't hesitate to reply.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 2

Hi WorkerBeeEl,

Thank you for your reply.

Yes, it is possible to copy cells that contain data validation rules into new workbooks in Excel. When you copy and paste a cell with data validation, the validation rule will usually be carried over to the destination cell as well. You can just use ctrl+c and ctrl+v to do it.

Have a nice day!

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 3

One small error I am running into is that I also need to copy 2 tabs next to the "template" tab into the new workbook, since those tabs contain the data validation values that are embedded in columns F and G

Resulting new workbook created with the current code does not copy over the data validation already set up in my "template"