How to copy data to another workbook: using VBA

When manipulating data in Microsoft Excel, the Move or Copy Sheet command is a quick and simple way to move or copy entire sheets to other locations either in the same file or in a different workbook. Alternatively, you can use VBA (Visual Basic for Applications) to automate the task and make it much less tedious. This article will introduce you to both methods of copying data to another workbook in Microsoft Excel.

How to transfer or copy data in the same workbook?

The first step is to select the sheet(s) that you would like to move or copy. To select multiple sheets, simply select your first sheet, and then, hold down the Control key while clicking the additional sheets that you'd like to copy.

On the Home tab of the upper toolbar, find the Cells group, and click Format. Under Organize Sheets, click Move or Copy Sheet.

A dialog box will open. In the Before list, you can either click the sheet before which you'd like to insert the moved or copied sheets, or you can click move to end to insert the sheets after the last sheet in your workbook.

To copy the sheets instead of moving them, select the Create a copy check box found in the Move or Copy dialog box.

How to transfer or copy data from different workbooks?

If you'd like to move or copy Excel worksheets to another workbook, you must first make sure that the target workbook is open in the same version of Microsoft Excel.

Select the sheets that you'd like to move or copy. Next, go to the Home tab on your toolbar and click the Cells group > Format. Under Organize Sheets, click Move or Copy Sheet.

A dialog box will open. In the To book list, choose to either move or copy the selected sheets to an existing workbook or to move or copy the sheets to a new workbook.

In the Before list, you can either click the sheet before which you'd like to insert the moved or copied sheets, or you can click move to end to insert the sheets after the last sheet in your workbook.

How to transfer or copy data using VBA?

Sub CopyOpenItems() 
   ' 
   ' CopyOpenItems Macro 
   ' Copy open items to sheet. 
   ' 
   ' Keyboard Shortcut: Ctrl+Shift+O 
   ' 
   Dim wbTarget            As Workbook 'workbook where the data is to be pasted 
   Dim wbThis              As Workbook 'workbook from where the data is to be copied 
   Dim strName             As String   'name of the source sheet/ target workbook 
'set to the current active workbook (the source book) 
   Set wbThis = ActiveWorkbook 
'get the active sheetname of the book 
   strName = ActiveSheet.Name 
'open a workbook that has same name as the sheet name 
   Set wbTarget = Workbooks.Open("C:\filepath\" & strName & ".xlsx") 
'select cell A1 on the target book 
   wbTarget.Range("A1").Select 
'clear existing values form target book 
   wbTarget.Range("A1:M51").ClearContents 
'activate the source book 
   wbThis.Activate 
'clear any thing on clipboard to maximize available memory 
   Application.CutCopyMode = False 
'copy the range from source book 
   wbThis.Range("A12:M62").Copy 
'paste the data on the target book 
   wbTarget.Range("A1").PasteSpecial 
'clear any thing on clipboard to maximize available memory 
   Application.CutCopyMode = False 
'save the target book 
   wbTarget.Save 
'close the workbook 
   wbTarget.Close 
'activate the source book again 
   wbThis.Activate 
'clear memory 
   Set wbTarget = Nothing 
   Set wbThis = Nothing 
End Sub