How to Copy The Data From One Workbook To Different Sheets In Microsoft Excel 2010

In this article, we will learn How to Copy The Data From One Workbook To Different Sheets In Microsoft Excel 2010.

To copy data from Excel to a file using VBA we need to access the word application using Excel VBA. In this tutorial we will learn how to open a word application, add a document and copy-paste data from excel to it.

In this article, we will use the Early Binding method to create an object of word application instead of using the late binding. You can read about it here in detail.

The Process Of Copying Excel Data To A Word File Using Excel VBA

To copy data from excel to a word file using VBA, we first need to open the Word Application of course. Then add a document to it (if you want a new document). Copy data from excel file. Select the paragraph on the doc and paste on it.Finally save and close the document. Each of these steps can be done easily from Excel. You will not need to interact with the Word Document.

Let's get started with an example without getting any further into theory. Because Iron Man said, "Sometimes you gotta run before you can walk".

Example : Paste Excel Data To Word Using VBA

The below code is an example of how you can copy some range of excel and paste it into a new word document and save it on the drive to use later

'VBA Code To Write to Copy data from Excel to A Document
Sub ExcelToWord()
   'Using Early Binding
    Dim wordApp As Word.Application
    Dim mydoc As Word.Document
        
    
    'Creating a new instance of word only if there no other instances
    Set wordApp = New Word.Application
    
    
    'Making word App Visible
    wordApp.Visible = True
   
    'Creating a new document
    Set mydoc = wordApp.Documents.Add()

    
    'copying the content from excel sheet

    ThisWorkbook.Worksheets("sheet1").Range("A1:g20").Copy

    'Pasting on the document

    mydoc.Paragraphs(1).Range.PasteExcelTable _
                               LinkedToExcel:=False, _
                               WordFormatting:=False, 
                               RTF:=False
    'saving the document
    mydoc.SaveAs2 "MyDoc"
    
    'closing the document
    mydoc.Close
   
    'Emptying the Clipboard 
    CutCopyMode = False
   
End Sub

Explanation of the Code:

Well I have explained each step in the code itself using comments but let's have some word about the lines we have used in this sub.

 'Created variables of word application and document type

    Dim wordApp As Word.Application

    Dim mydoc As Word.Document

Here we have declared two variables of the required types. We can do this because  we have already added the reference to the word application. You can do this by going to the tools in the menu. Find references option and then look for the word reference.

  'Creating a new instance of word only if there no other instances
    Set wordApp = New Word.Application
    
    
    'Making word App Visible
    wordApp.Visible = True
   
    'Creating a new document
    Set mydoc = wordApp.Documents.Add()

In the first line above, we are intentiating the wordApp variable with an object of type Word.App using the New keyword. This will open the Word Application.

In the second line we are making the word application visible so that we can work with it.

In the next line, we add a new document to the word application using Word.Documents.Add() function. This is stored in the mydoc variable.

 'copying the content from excel sheet

ThisWorkbook.Worksheets("sheet1").Range("A1:G20").Copy

Here we are simply copying a range from excel. You must have done it before. It is stored on the clipboard.

  'Pasting on the document

    mydoc.Paragraphs(1).Range.PasteExcelTable _
                               LinkedToExcel:=False, _
                               WordFormatting:=False, 
                               RTF:=False

We are using the PasteExcelTable method of Paragraph.Range class of mydoc to paste data from clipboard.

'saving the document
    mydoc.SaveAs2 "MyDoc"
    
    'closing the document
    mydoc.Close
   
    'Emptying the Clipboard 
    CutCopyMode = False

We are saving the document with the name MyDoc. Then we close the document using the Close function. Finally we release the clipboard to be used by others.

Hope this article about How to Copy The Data From One Workbook To Different Sheets In Microsoft Excel 2010 is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

VBA variables in Excel : VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.

Excel VBA Variable Scope : In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.

ByRef and ByVal Arguments : When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.

Add And Save New Workbook Using VBA In Microsoft Excel 2016 : In this code, we first created a reference to a workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.