Mass Export in Excel: How to Save Same Sheet & Change Variable

Let’s assume the following situation: You have created an Excel file and want to export one sheet from it – many times with different settings. For example: Each sales person should receive their own sales numbers. The problem is that you have many sheets to export. And in practice the following happens: You have just finished saving all files separately when you have to incorporate a small change. What do you do? So, let’s explore in this article, how to mass export single (or multiple) worksheets with changing one variable.

Summary for mass export

In this article you learn how to mass export the same worksheet(s) multiple times – changing one variable/cell from a list each time.

• If you want to export your worksheet only a couple of times (~less than 6), use the manual way.
• If you feel comfortable editing a basic VBA macro, go with method 2.
• All other times, use method 3 with Professor Excel Tools (you can use it for free for 7 days).

General steps to export the same sheet with different variable in bulk

Mass export example in this article

Before we start, let’s take a look at the general example: You have prepared a small form containing the sales numbers for each sales person.

When you change the name in cell C7 from the drop down list, all values on the worksheet change accordingly. The sheet is filled with SUMIFS / VLOOKUP / INDEX/MATCH functions from the second worksheet “Sales_Data” containing all the data.

The problem: Because each sales person is only allowed to see his or her numbers, you have to split the file.

General steps for exporting worksheet

All following methods will apply these steps more or less automatically.

1. Set your variable so that your sheet contains the correct values.
2. Copy the worksheet or sheets to a new Excel workbook.
3. Break the links to the original Excel file.
4. Save the file under a new name.
5. Start again with number one…

Method 1: Do it manually with these steps

In this section you learn how to mass export the same worksheet step-by-step. It’s not especially fast but it summarizes all steps for you. If you only have to do it five or less than five times, maybe this is even the fastest method.

1. Select all worksheets you want to export and right-click on one of them.
2. Click on “Move or Copy…”
3. In the Move or Copy window, select “(new book)” in the “To book” field.
4. Important: Check “Create a copy”. Otherwise the original worksheet(s) will be moved. Confirm with OK.

Now you will see a new workbook. It probably still contains links to the original workbook which – in most cases – need to be broken.

5. Go to the Data ribbon and click on “Edit Links”.
6. Select the link to your original Excel file.
7. Click on “Break Link” and then Close. If you have problems breaking all links, please refer to this article.

After that, you can save the file and proceed with the next export.

Method 2: Use this VBA macro to save the same worksheet over and over again to hard drive

Especially if you have to work on many worksheets to export, the first method above takes a long time. That is usually the moment, when we start to consider other options, namely using a VBA macro.

Please find a VBA macro below. Copy and paste it into a new module (here is how to do that).

Before you just go ahead and run the module, you need to slightly adapt it. You have to fill in your sheet names and ranges, for both the list and the cell that is supposed to be changed:

Here is the code for you to copy and paste:

Sub MassExport()

    Dim newWorkbook As Workbook
    Dim nameOfExportFolder As String, worksheetNameWithList As String, rangeOfList As String, worksheetNameOfCellVariable As String, cellOfVariable As String, completeFileName As String
    Dim listRange As Range
        
    'On which worksheet is your list located?
    worksheetNameWithList = "Sales_Data"
    
    'Which cell range on the above defined worksheet is your list located?
    rangeOfList = "F5:F10"
    
    'On which worksheet is the variable cell that should be changed with each export?
    worksheetNameOfCellVariable = "Form_per_Person"
    
    'What is the cell address of the variable that should change with each export?
    cellOfVariable = "C7"
    
    'In which folder do you want to export the worksheets to?
    nameOfExportFolder = "C:\Users\hschi\Documents\Export"
 
    'If you receive an error message here, the export folder might exist already. In such case either delete or rename it. Or choose a different name above
    MkDir nameOfExportFolder
    
    Set listRange = Worksheets(worksheetNameWithList).Range(rangeOfList)
    
    Dim variableCell As Range
    Set variableCell = Worksheets(worksheetNameOfCellVariable).Range(cellOfVariable)
    
    For Each cell In listRange
        variableCell.Value = cell.Value
        Application.Calculate
        
        ActiveWindow.SelectedSheets.Copy
        completeFileName = nameOfExportFolder & "\" & cell.Value & ".xlsx"
        Set newWorkbook = Application.Workbooks.Item(Application.Workbooks.Count)
        
        breakWorkbookLinks
        newWorkbook.SaveAs completeFileName
        newWorkbook.Close False, completeFileName

    Next
 
    MsgBox "Worksheets exported as new workbooks to folder: " & nameOfExportFolder

End Sub

Sub breakWorkbookLinks()
    'Break all workbook links in the exported file
    
    Dim aLinks As Variant
    allLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(allLinks) Then
        For i = 1 To UBound(allLinks)
            ActiveWorkbook.BreakLink Name:=allLinks(i), Type:=xlExcelLinks
        Next i
    End If

End Sub

Method 3: Excel add-in to comfortably mass export Excel sheet

The most comfortable method: Use an Excel add-in. Our add-in “Professor Excel Tools” can do all that for you. It comes with a powerful Export feature.

Here is the demo:

The steps in detail:

0. Make sure that all worksheets that should be exported multiple times are selected (for example, if each file should have 2 or 3 worksheets, select them).
1. Click on “Export Manager” on the Professor Excel ribbon.
2. Select the cell to change. In this case it is cell C7.
3. Select the list of variables.
4. Export settings: You can either create copies in the current workbook or export the selected sheets into separate Excel- or PDF files.
5. Click on Start.

Download

Please feel free to download the example from this article here.

Image by WorldInMyEyes from Pixabay