Copy Data to Another Workbook in Excel

August 2016

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.


Move 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.

Move or Copy Data in 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.

Move 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

See also :

This document entitled « Copy Data to Another Workbook in Excel » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.