How to Copy Range Into a New Workbook Using VBA

In this lesson you can learn how to copy a range from an existing workbook to a new workbook. Existing workbook should stay open when we run the program. And we are going to paste the range to a new workbook. So the program will create a workbook automatically before paste. If you want to copy a range from a closed workbook to a new workbook then first you need to open the closed workbook using VBA.

Macros included in this lesson

1. Macro to copy range with formatting
2. Macro to copy range with original widths
3. Macro to copy range as values (two macros using two different approaches)

So let’s consider this sample workbook. Suppose the name of the workbook is “Data File.xlsx” and the name of the sheet is “Sheet1”. So we have the data in range B5:E12

You can copy ranges from any Excel file format such as .xlsx, .xls, .xlsm etc. But you can save code only inside the .xlsm files. Or else add the macro to the personal macro workbook.

How To Record Macros In Personal Macro Workbook

Now what we are going to do is create a new workbook and copy paste this data into the range starting from cell A1.

First create two variables to hold the workbooks.

Dim WB_SourceFile As Workbook
Dim WB_New As Workbook

Then assign the Data File.xlsx worksheet to the variable WB_SourceFile

Set WB_SourceFile = Workbooks("Data File.xlsx")

Copy range B5:E12 from sheet1 of the Data File.xlsx

WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy

Create a new workbook and assign it to the variable WB_New

Set WB_New = Workbooks.Add

Select cell A1 of the activesheet of the newly created workbook.

WB_New.ActiveSheet.Range("A1").Select

Now you can paste the data.

ActiveSheet.Paste

So this is the final sub procedure.

Sub CopyPasteRange()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Select
     ActiveSheet.Paste

End Sub

Here is the result of the above macro.

As you can see the width of columns of the new workbook differ from the original file. However we can use the PasteSpecial method to paste the values with original widths. Use the below macro if you want to paste the data with original column widths.

Sub CopyPasteRange_OriginalWidth()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Select
     Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
          SkipBlanks:=False, Transpose:=False
     ActiveSheet.Paste

End Sub

In above examples range was copy pasted with formatting like colors, outlines etc. But you can also copy paste only values from the original workbook to the new workbook. This is how you can do it.

Sub CopyPasteRange_PasteValues()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False

End Sub

This is the result you will get.

Sometimes you may want to copy only a few values from an existing workbook to a new workbook. Here is an example.

Suppose this is the Sheet2 of the Data File.xlsx file. If we want to copy paste these two rates in the cell A1 and A2 of a new workbook, then we can do it easily using the Range.Value property as follows.

Sub CopyPasteValues()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Value = WB_SourceFile.Worksheets("Sheet2").Range("C4").Value
     WB_New.ActiveSheet.Range("A2").Value = WB_SourceFile.Worksheets("Sheet2").Range("C5").Value
End Sub