Creating new Excel files based on values of a column

Copper Contributor

I have an excel file created from a form filled by a few thousand people. This form has a couple of questions and one of these questions have more than 60 possible answers. The person filling the form can choose 1 to 60 from a list. Let's assume I give this column the title "Community". There are currently nearly 1500 people who filled the form. Some of them have chosen one community while others several communities, and if the person has chosen several communities, they are listed in cell associated with that column separated by a comma like this:

Column A (Timestamp) | Column B (email) | Column C (Yes or No) | Column D (Community)|Column E (Name Surname) | Column F (Cell Phone) | GHIJKLMNO (other answers)





a, b, d

c, f

a, b, c, d, e, f



I want to achieve this:


Create a new excel file for each community (currently more than 60) that can be chosen in question "Community".

So newly created excel files should have the following filenames:

A Club

B Club

C Club


I want only information of the people who have chosen A Community to be included in the new excel file named "A Club", but I also want people who have chosen A community and also other communities to be on this excel file. (I want rest of the cells in rows for that person left intact). I also want the have the first rows containing titles to be on top of each new excel file. I also would like the newly created excel files to be converted into tables (with first row being titles)


Can someone guide me step by step how to create something (maybe a script) which does all of these with a single click so that someone old who doesn't know how to do this by filtering can do this?


After the excel files are created, I want them to be sent to a google drive and only the manager of that community should be able to download that file)

2 Replies


To achieve this, you can use Microsoft Excel's built-in features along with VBA (Visual Basic for Applications) scripting. Here is a step-by-step guide on how to create new Excel files for each community, filter the data, and send them to Google Drive. Note that the process involves multiple steps, and you may need to have some VBA coding skills.

Step 1: Copy and Filter Data

  1. In your original Excel file, create a new worksheet for each community (e.g., "A Club," "B Club," etc.).
  2. Filter the data for each community using Excel's AutoFilter or Filter function. You can do this by clicking on the filter icon in the "Community" column and selecting the community you want to filter.
  3. Copy the filtered data and paste it into the corresponding community worksheet.
  4. To convert each worksheet to a table (Excel Table), select the data, go to the "Insert" tab, and click on "Table."

Step 2: VBA Script to Create Individual Files

Now, you can use a VBA script to automate the process of creating individual Excel files for each community. Here is a simplified example:

Sub CreateCommunityFiles()
    Dim ws As Worksheet
    Dim community As Range
    Dim newWorkbook As Workbook
    ' Loop through each community worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Overview" Then ' Skip non-community sheets
            ' Set the range of unique community values
            Set community = ws.Range("D2:D" & ws.Cells(Rows.Count, "D").End(xlUp).Row)
            ' Loop through each unique community
            For Each cell In community
                ' Create a new workbook
                Set newWorkbook = Workbooks.Add
                ' Copy headers from the original worksheet
                ws.Rows(1).Copy Destination:=newWorkbook.Sheets(1).Rows(1)
                ' Filter and copy data for the current community
                ws.UsedRange.AutoFilter Field:=4, Criteria1:=cell.Value
                ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=newWorkbook.Sheets(1).Cells(2, 1)
                ' Remove filter in the original worksheet
                ws.AutoFilterMode = False
                ' Save the new workbook with the community name
                newWorkbook.SaveAs "Path\to\save" & "\" & cell.Value & ".xlsx"
                newWorkbook.Close SaveChanges:=False
            Next cell
        End If
    Next ws
End Sub

Please note that you will need to adapt the script to your specific file structure, and you may need to make changes based on your data and folder paths.

Step 3: Share Files on Google Drive

Once you have created individual Excel files, you can share them on Google Drive:

  1. Upload the Excel files to your Google Drive.
  2. Right-click on each file and select "Share."
  3. Set the sharing options to allow only the manager of each community to download the file. You can do this by entering the manager's email address in the sharing settings.

By following these steps, you should be able to automatically create individual Excel files for each community, filter the data, and share them on Google Drive with the specified access permissions. The text, steps and the vba code were edited with the help of AI.


My answers are voluntary and without guarantee!


Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.