Skip to main content

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

Workbook containing the data to be copy pasted

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.

Range was pasted to the new workbook

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.

Copy paste only values to the new file

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

Sheet of a existing workbook containing few values

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

Values pasted to the new workbook

Comments

Post a Comment

Popular posts from this blog

How to Add a Formula to a Cell Using VBA

In this lesson you can learn how to add a formula to a cell using vba. There are several ways to insert formulas to cells automatically. We can use properties like Formula, Value and FormulaR1C1 of the Range object. This post explains five different ways to add formulas to cells. Table of contents How to add formula to cell using VBA Add formula to cell and fill down using VBA Add sum formula to cell using VBA How to add If formula to cell using VBA Add formula to cell with quotes using VBA Add Vlookup formula to cell using VBA We use formulas to calculate various things in Excel. Sometimes you may need to enter the same formula to hundreds or thousands of rows or columns only changing the row numbers or columns. For an example let’s consider this sample Excel sheet. In this Excel sheet I have added a very simple formula to the D2 cell. =B2+C2 So what if we want to add similar formulas for all the rows in column D. So the D3 cell will have the formula
Post a Comment
Read more

Download a file from url using VBA

Sometimes our Excel VBA applications need to interact with websites. Downloading a file through a URL is a typical example. In this lesson you can learn how to do that using XMLHttpRequest and ADODB.Stream object. XMLHttp is used to request the data from the web server. Once we receive the data from the server, the ADODB.Stream object is used to write that data to a file. You can use this method to download file types such as image files, csv files etc. So let’s start writing our macro to download a file using the Uniform Resource Locator (URL). Let’s name our Sub procedure as “DownloadFileFromURL” Sub DownloadFileFromURL() End Sub First we need to declare a few variables. Dim FileUrl As String Dim objXmlHttpReq As Object Dim objStream As Object Next assign the URL of the file to the FileUrl variables FileUrl = "URL of your file" For an example if your file URL is https://www.example.com/images/chart.jpg then you can assig
Read more

Fill a Listbox From an Array

In this lesson you will learn how to fill a Listbox from an array. Listboxes are a very useful control type available in the VBA toolbox. You can use the listboxes for search and view functions of the VBA programs. First let’s fill the listbox using a multidimensional array. After that we can try it with a one dimensional array. This is the sample Excel sheet I’m going to use for this lesson. And here is the listbox we are going to fill with the array. So we have data in three columns and fourteen rows. First, we are going to add this data to an array. After that we can add the array to our listbox in the VBA form. You might wonder why we need to add the data to an array. Because data can be directly added to the listbox from the worksheet using VBA. Yes it is possible. But here our objective is to learn how to add data to a listbox from an array. Because there are situations where we need to add the data which is not available in worksheets to listboxes using VBA. For
Post a Comment
Read more