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.