Copy Selection To Other Workbook Excel VBA Macro

This is a simple example to copy the selected range into a newly created workbook. We use object variables to easily manage both workbooks. Then we save the new workbook in the same location and close it.

Macro/VBA code:

 

  Sub CopySelectionToOtherWorkbook()

      Dim wb1 As Workbook, wb2 As Workbook

      Dim myPath As String, myFile As String

      Set wb1 = ThisWorkbook

      wb1.Activate

      Selection.Copy

      Workbooks.Add

      Set wb2 = ActiveWorkbook

      wb2.ActiveSheet.Paste

      myPath = wb1.Path

      myFile = "\wb2filename.xlsx"

      wb2.SaveAs Filename:=myPath & myFile

      wb2.Close

  End Sub

 

Macro explained:

• First we declare two workbook object variables (wb1, wb2) and two strings to hold the path and file name of the new workbook.
• We set the workbook running the macro and having the source data to the object variable wb1 and activate it (just in case the macro is run from other workbook). Then we copy whatever range was selected.
• Now we create or add a new workbook. Upon creation that workbook becomes active. The default name should be Book1.xlsx, or Book2, Book3, etc if there are previously added workbooks.
• We set the new workbook to the object variable wb2 and then we paste the previously copied selection into the active sheet.
• Finally, we define the path and name to save the new workbook. The path is that of the source workbook (wb1), but it can be set to a specific location with: myPath = “C:\Users\username\Documents\Excel\Excel Macro Class”. In this case, the name is wb2filename.xlsx, but that can be changed to any name.

This is how we copy the selected range to other workbook in Excel with VBA macros.

 

Other examples:

• How to Add Shapes To Selection with Excel VBA macros
  
• How to List All Files in Folder with Excel VBA macros
• How to Copy Filtered Data To New Sheets with Excel VBA macros