How to save a selection as individual file in Excel 2007/2010?

When we deal with a long worksheet, we may want to save only a selected range instead of a whole workbook as a new Excel file, such as sending different selections of a worksheet to different persons. We will introduce methods to save selections in a worksheet as individual Excel files.

Save selections as individual Excel files with copying and pasting
Save selections as individual Excel files with VB Macro
Save Selections as individual Excel files with Kutools for Excel

Save selections as individual files with copying and pasting

The most common method is to copy the selection that you want to save as an individual file in Excel, create a new workbook, and paste the copied selection on it, then save it.

However, you may lose some format styles in the selection during copying and pasting, such as row height, etc.

Save selections as individual files with VB Macro

Using Macro is an easy way to save the selections as individual files without copying and pasting to a new workbook.

Select a range in the current worksheet you want to save as individual Excel file;
Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window. Click Insert > Moudle, and paste the following macro in the Moudle Window.
Click to execute the operation.

Sub ExportRangeAsWorkBook()

Dim FileName As Variant
Dim RangeToExport As Range
Dim FN As String
Dim FFilter As String
Dim NewWorkBook As Workbook
Dim UserSheets As Integer
Dim NewWorksheet As Worksheet

Set RangeToExport = Application.Selection
Set RangeToExport = Intersect(RangeToExport, RangeToExport.Parent.UsedRange)

If RangeToExport.Parent.ProtectContents Then
MsgBox "Cannot export the range." & vbCrLf & vbCrLf & "The Worksheet is protected. You will need to unprotect the Worksheet.", vbCritical, APPNAME
Exit Sub
End If

FN = Replace(RangeToExport.Address & "." & xlsx, ":", "-")
FN = Replace(FN, "$", "")
FN = Replace(FN, ".", "")
If InStr(1, FN, "!") = 0 Then FN = ActiveSheet.Name & "!" & FN
FFilter = "Excel Workbooks (*.xlsx),*.xlsx"

FileName = Application.GetSaveAsFilename(InitialFileName:=FN, FileFilter:=FFilter, Title:="Select a name and location for the exported range")

If FileName <> False Then
Application.ScreenUpdating = False
UserSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set NewWorkBook = Application.Workbooks.Add
Set NewWorksheet = NewWorkBook.Sheets(1)
RangeToExport.Copy
NewWorksheet.Paste
With NewWorkBook
Application.DisplayAlerts = False
.SaveAs FileName
.Close False
Application.DisplayAlerts = True
End With
Application.ScreenUpdating = True
End If
End Sub

Using Macro may be a little hard for starter of Microsoft Excel, and we will introduce another easy and convenient way provided with Kutools for Excel.

Save Selections as individual files with Kutools for Excel

Kutools for Excel's Export Range to File tool can help us conveniently save selections as individual files quickly.

Step 1: Click the Import / Export button under Kutools tab, and click the Export Range to File command item. See screenshot (Fig. 1).

doc-save-selection-2

Fig. 1: Export Range to File

Step 2: Click the button and select the range that you want to save it as an individual file.

Step 3: Check the Excel workbook option in File format section. See screenshot (Fig. 2).

doc-save-selection-1

Fig. 2: Export Range to File dialog box

Step 4: Click OK.

Then it will pop up a Select a name and location for the exported range dialog box similar as Save As dialog box, enter a name for the selection, and select a folder where you will save it in.

The Export Range to File tool of Kutools for Excel can save all formatting in the selections.

Kutools for Excel's Export Range to File tool makes it possible to easily export a range of date to a file, either an Excel workbook, a CSV, a simple HTML or all formatting HTML.

Kutools for Excel includes more than 80 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.