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
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.
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.
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
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)
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
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.
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).
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).
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.
- How to export Excel data to CSV files in Excel 2007/2010?
- How to export Excel data to Text files in Excel 2007/2010?