How to Copy a Selected Range to New Workbook in Excel

If you work with large amounts of data in Excel, it is likely that you have needed to copy a selected range of cells to a new workbook at some point. While you can manually copy and paste the range, this process can be time-consuming and error-prone, especially if you need to repeat the task frequently.

Fortunately, there are several ways to automate the process of copying a selected range to a new workbook in Excel. In this post, we will introduce two methods to achieve this task – the first one is the manual method, and the second one is using VBA code with Application.InputBox to select the range.

Copy a Selected Range to a New Workbook Manually

If you want to copy a selected range to a new workbook manually in Excel , you can follow these steps:

Step1: Select the range of cells you want to copy.

Step2: Press Ctrl + C on your keyboard to copy the selected range.

Step3: Click on the File tab in the ribbon menu, then select New to create a new workbook.

Step4: In the new workbook, click on the first cell where you want to paste the copied range.

Step5: Press Ctrl + V on your keyboard to paste the copied range.

Copy a Selected Range to a New Workbook with VBA Code

You can also use the VBA Code to copy a selected range to a new workbook in Excel, it will use the Application.Inputbox function to select the range. Just do the below steps:

Step1: Open the Excel workbook that contains the range you want to copy.

Step2: Press Alt + F11 on your keyboard to open the Visual Basic Editor.

Step3: In the Visual Basic Editor, click on Insert from the top menu and select Module to insert a new module.

Step4: Copy and paste the VBA code provided in the module.

Sub CopyRangeToNewWorkbook_excelgeek()
    Dim myRange As Range
    Dim newWorkbook As Workbook
    
    'Prompt user to select range to copy
    Set myRange = Application.InputBox(prompt:="Select the range to copy", Type:=8)
    
    'Create a new workbook
    Set newWorkbook = Workbooks.Add
    
    'Copy the selected range to the new workbook
    myRange.Copy Destination:=newWorkbook.Sheets(1).Range("A1")
    
    'Activate the new workbook
    newWorkbook.Activate
End Sub

Step5: Press F5 or Run from the top menu to run the macro.

Step6: In the pop-up window, select the range you want to copy by clicking and dragging your mouse over the cells. Click on the OK button to start the macro.

Step7: Excel will create a new workbook and copy the selected range to the new workbook.