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.
- Set your variable so that your sheet contains the correct values.
- Copy the worksheet or sheets to a new Excel workbook.
- Break the links to the original Excel file.
- Save the file under a new name.
- 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.
- Select all worksheets you want to export and right-click on one of them.
- Click on “Move or Copy…”
- In the Move or Copy window, select “(new book)” in the “To book” field.
- 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.
- Go to the Data ribbon and click on “Edit Links”.
- Select the link to your original Excel file.
- 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:
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
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
completeFileName = nameOfExportFolder & "\" & cell.Value & ".xlsx"
Set newWorkbook = Application.Workbooks.Item(Application.Workbooks.Count)
newWorkbook.Close False, completeFileName
MsgBox "Worksheets exported as new workbooks to folder: " & nameOfExportFolder
'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
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
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:
- 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).
- Click on “Export Manager” on the Professor Excel ribbon.
- Select the cell to change. In this case it is cell C7.
- Select the list of variables.
- Export settings: You can either create copies in the current workbook or export the selected sheets into separate Excel- or PDF files.
- Click on Start.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Please feel free to download the example from this article here.