How to Download One Sheet From Excel?

Sometimes, you may need to download a single sheet from a workbook and save it as a separate file to share or use independently from the original workbook.

Downloading one sheet allows you to isolate and share only the relevant information without exposing the entire workbook.

In this tutorial, I will show you eight scenarios of downloading one sheet from Excel.

Table of Contents

Move or Copy Command to Download a Specific Worksheet

Suppose you have an open workbook named ‘Annual Sales Report’ containing three worksheets: ‘California,’ ‘Texas,’ and ‘New York.’

You can use the below steps to download the ‘Texas’ worksheet and save it as a separate file:

Right-click the ‘Texas’ worksheet tab and choose ‘Move or Copy’ on the shortcut menu.

On the ‘Move or Copy’ dialog box, open the ‘To book’ drop-down menu and choose ‘(new book).’

Click OK. Your new workbook opens with the moved worksheet.

Note: This process moves your worksheet from the original workbook. Select ‘Create a copy’ in the ‘Move or Copy’ dialog box to keep the worksheet in both workbooks.

Save the new workbook to a preferred name and location.

This method works well when you want to download only a couple of worksheets in the workbook. In case you have a lot of worksheets and you want to download each sheet separately as a workbook, it’s better to use the VBA methods covered next.

Also read: 3 Easy Ways to Duplicate Sheet in Excel (Shortcuts + VBA)

Excel VBA to Download a Specific Worksheet

Assuming you have a workbook named ‘Annual Sales Report’ containing three worksheets: ‘California,’ ‘Texas,’ and ‘New York.’

You can use the following VBA code to download a copy of the ‘New York’ worksheet and save it as ‘New York Sales’ workbook in the ‘Reports’ folder:

Sub DownloadSheetToNewWorkbook()

    ' Disables display alerts and screen updating for smoother execution
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    ' Declares variables for the worksheet and new workbook
    Dim ws As Worksheet
    Dim newWorkbook As Workbook

    ' Sets the worksheet from the current workbook
    Set ws = ThisWorkbook.Sheets("New York")

    ' Creates a new workbook
    Set newWorkbook = Workbooks.Add

    ' Copies the specified worksheet to the new workbook
    ws.Copy Before:=newWorkbook.Sheets("Sheet1")

    ' Deletes the default "Sheet1" in the new workbook
    newWorkbook.Sheets("Sheet1").Delete

    ' Saves the new workbook with the specified file name
    newWorkbook.SaveAs "C:\Reports\New York Sales.xlsx"

    ' Closes the new workbook and saves changes
    newWorkbook.Close SaveChanges:=False

    ' Re-enables display alerts and screen updating
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

The above code copies a specific worksheet (“New York”) from the current workbook to a new workbook and then save this new workbook as a separate file (“C:\Reports\New York Sales.xlsx”).

It begins by disabling display alerts and screen updating for more efficient script execution. The script then creates a new workbook and copies the “New York” sheet into it, placing it before the default “Sheet1” of the new workbook.

After copying, it deletes the now redundant “Sheet1” from the new workbook. Finally, the new workbook is saved with the designated file name and closed, and the script re-enables display alerts and screen updating.

Note: This code will keep a copy of the ‘New York’ worksheet in both workbooks. If you want to move the worksheet from the original workbook, change the seventh line of the code to ‘ws.Move Before:=newWorkbook.Sheets(“Sheet1”)‘

Explanation of the Code

The code creates a new workbook, copies a specific sheet (“New York”) from the current workbook to the new workbook, deletes the default Sheet1 in the new workbook, saves the new workbook, and closes it. It speeds up the process by temporarily turning off display alerts and screen updating.

Here’s a breakdown of some lines of the code:

Application.DisplayAlerts = False: This line turns off display alerts, preventing Excel from showing pop-up messages (e.g., confirmation dialogs) during the execution.
Application.ScreenUpdating = False: This line turns off screen updating, which improves the performance of the code by preventing Excel from updating the screen with each change.
Set ws = ThisWorkbook.Sheets(“New York”): This line sets the variable ‘ws’ to the ‘New York’ worksheet.
Set newWorkbook = Workbooks.Add: This statement creates a new workbook.
ws.Copy Before:=newWorkbook.Sheets(1): This line copies the specified sheet to the new workbook, placing it before Sheet1.
newWorkbook.SaveAs “C:\Reports\New York Sales.xlsx“: This line saves the new workbook as ‘New York Sales’ in the ‘Reports’ folder.
newWorkbook.Sheets(“Sheet1”).Delete: This statement deletes the default Sheet1.
newWorkbook.Close SaveChanges:=True: This line closes the new workbook without saving any changes.

Also read: How to Save an Excel Table as Image?

VBA to Download the Active Worksheet

Suppose you need to save a copy of the active worksheet as a new workbook in the ‘Reports’ folder with the name of the active worksheet. You can use the following code to accomplish this task:

Sub DownloadAndSaveActiveSheet()

    ' Disables display alerts and screen updating for smoother execution
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    ' Declares variables for the new workbook, current worksheet, and file names
    Dim newWorkbook As Workbook
    Dim currentSheet As Worksheet
    Dim newWorkbookName As String
    Dim newFileName As String

    ' Sets the current active sheet
    Set currentSheet = ActiveSheet

    ' Stores the name of the current sheet for the new workbook's name
    newWorkbookName = ActiveSheet.Name

    ' Creates a new workbook
    Set newWorkbook = Workbooks.Add

    ' Copies the active sheet to the new workbook
    currentSheet.Copy Before:=newWorkbook.Sheets("Sheet1")

    ' Deletes the default "Sheet1" in the new workbook
    newWorkbook.Sheets("Sheet1").Delete

    ' Constructs the new file name with path
    newFileName = "C:\Reports\" & currentSheet.Name & ".xlsx"

    ' Saves the new workbook with the constructed file name
    newWorkbook.SaveAs newFileName

    ' Closes the new workbook without saving changes (as it's already saved)
    newWorkbook.Close SaveChanges:=False

    ' Re-enables display alerts and screen updating
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

The above VBA code is designed to create a new workbook and save the currently active worksheet in it as a separate file.

It first disables display alerts and screen updating to enhance the script’s performance. The subroutine then references the active worksheet, creates a new workbook, and copies the active sheet into this new workbook, replacing the default “Sheet1.”

The new workbook is then saved with a filename that includes the name of the active sheet, and the location is specified as “C:\Reports”.

After saving, the new workbook is closed without saving changes (since it’s already been saved). Finally, the script re-enables display alerts and screen updating.

Also read: Save Excel Chart as Image (High Resolution)

Download a Worksheet and Save it as PDF

You may want to download a specific worksheet and save it as a PDF.

Suppose you have a worksheet named ‘California’ in the current workbook.

You can use the following code to download the worksheet and save it as a PDF named ‘California’ in the folder ‘Individual PDFs’:

Sub SaveSpecificSheetAsPDF()

    ' Declares variables
    Dim pdfFileName As String
    Dim sheetToSave As Worksheet

    ' Sets the PDF file name and location
    pdfFileName = "C:\Individual PDFs\California"

    ' Sets the specific worksheet to be saved as PDF
    Set sheetToSave = ThisWorkbook.Sheets("California")

    ' Exports the specified sheet as a PDF file
    sheetToSave.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName

End Sub

The above VBA code is designed to save a specific worksheet, named “California,” from the current workbook as a PDF file.

The file is saved with the designated name “California” in the directory “C:\Individual PDFs”.

The subroutine uses the ExportAsFixedFormat method to perform the conversion, specifying the file type as PDF and the file name as pdfFileName.

Also read: How to Save Selection in Excel as PDF?

Download Active Worksheet and Save it as PDF

You may be required to download the active worksheet and save it as a PDF.

You can use the following code to download the active worksheet and save it as a PDF named ‘Attachment1’ in the folder ‘Email Attachments’:

Sub SaveActiveSheetAsPDF()

    ' Declares a variable for the PDF file name
    Dim pdfFileName As String

    ' Sets the PDF file name and location
    pdfFileName = "C:\Email Attachments\Attachment1.pdf"

    ' Exports the active sheet as a PDF file
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName

End Sub

The VBA code is designed to save the currently active worksheet in Excel as a PDF file.

The file is saved with the name “Attachment1.pdf” in the directory “C:\Email Attachments”.

This is accomplished using the ExportAsFixedFormat method, specifying the file type as PDF and the filename as pdfFileName.

This method is particularly useful for quickly converting the active sheet into a PDF format, which is ideal for creating attachments for emails, reports, or documentation purposes in a format that is easily accessible and widely used.

Also read: How to Convert PDF to Excel without Software?

Download a Worksheet and Save it as a CSV

You may need to download a specific worksheet and save it as a CSV.

Suppose you have a worksheet named ‘California’ in the current workbook. You can use the following code to download the worksheet and save it as a CSV named ‘California’ in the folder ‘Individual CSVs’:

Sub SaveSpeficSheetAsCSV()

    ' Declares variables
    Dim csvFileName As String
    Dim sheetToSave As Worksheet

    ' Sets the CSV file name and location
    csvFileName = "C:\Individual CSVs\California"

    ' Sets the specific worksheet to be saved as CSV
    Set sheetToSave = ThisWorkbook.Sheets("California")

    ' Saves the specified sheet as a CSV file
    sheetToSave.SaveAs Filename:=csvFileName, FileFormat:=xlCSV

End Sub

The VBA code is designed to save a specific worksheet, named “California,” from the current workbook as a CSV (Comma-Separated Values) file.

The file is saved with the name “California” in the directory “C:\Individual CSVs”. The subroutine uses the SaveAs method to perform the saving operation, specifying the filename as csvFileName and the file format as xlCSV.

Also read: What is VBA in Excel?

Download the Active Worksheet and Save it as CSV

You may be required to download the active worksheet and save it as a CSV. You can use the following code to download the active worksheet and save it as a CSV named ‘Report1’ in the folder ‘Email Attachments’:

Sub SaveActiveSheetAsCSV()

    ' Declares a variable for the CSV file name
    Dim csvFileName As String

    ' Sets the CSV file name and location
    csvFileName = "C:\Email Attachments\Report1"

    ' Saves the active sheet as a CSV file
    ActiveSheet.SaveAs Filename:=csvFileName, FileFormat:=xlCSV

End Sub

The SaveActiveSheetAsCSV subroutine is designed to save the currently active worksheet in Excel as a CSV (Comma-Separated Values) file.

The file is saved with the name “Report1” in the directory “C:\Email Attachments”.

This is accomplished using the SaveAs method, specifying the filename as csvFileName and the file format as xlCSV.

Also read: How to Merge Two Excel Files?

Split a Workbook’s Worksheets into Separate Workbooks

Suppose you want to split the current workbook’s worksheets into separate workbooks, saving each workbook in the worksheet’s name in a folder called ‘Individual Reports.’

You can use the following code to accomplish the operation:

Sub SplitWorksheetsToWorkbooks()

    ' Disables display alerts and screen updating
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    ' Declares variables for worksheet, new workbook, and the file path
    Dim ws As Worksheet
    Dim newWorkbook As Workbook
    Dim path As String

    ' Sets the path where new workbooks will be saved
    path = "C:\Individual Reports\"

    ' Loops through each worksheet in the current workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Creates a new workbook
        Set newWorkbook = Workbooks.Add

        ' Copies the current worksheet to the new workbook
        ws.Copy Before:=newWorkbook.Sheets("Sheet1")

        ' Saves the new workbook with the name of the worksheet
        newWorkbook.SaveAs path & ws.Name & ".xlsx"

        ' Deletes the default "Sheet1" in the new workbook
        newWorkbook.Sheets("Sheet1").Delete

        ' Closes the new workbook and saves changes
        newWorkbook.Close SaveChanges:=True
    Next ws

    ' Re-enables display alerts and screen updating
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

The above VBA code is designed to separate each worksheet in the current workbook into its own new workbook.

For each worksheet in the original workbook, the script creates a new workbook, copies the worksheet into it (replacing the default “Sheet1”), and then saves the new workbook in a specified directory (“C:\Individual Reports”) with the worksheet’s name as the file name.

After saving, the new workbook is closed. This process is repeated for each worksheet in the original workbook.

In this tutorial, I showed you some ways to download a sheet from Excel as a separate workbook.

I hope you found the tutorial helpful.

Other Excel articles you may also like: