Copy Data to Another Workbook in Excel

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

Image: © Dzmitry Kliapitski - Shutterstockom