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
Need more help with Excel? Check out our forum!
- Copy data from one workbook to another vba
- Macro to copy data from one workbook to another based on criteria [solved] > Forum - Excel
- Copy Worksheet to another Workbook (unopened) [solved] > Forum - Excel
- Transfer Data Between Excel Spreadsheets
- Excel - Exporting data to another spreadsheet
- Insert picture in Excel: cell, shortcut, using formula
- How to show or hide formula bar in Excel: VBA, shortcut
- How to enter multiple lines in a single Excel cell
- How to automatically transfer data between sheets in Excel
- VBA Excel color codes: index number, list
- Repeat rows in Excel: based on cell value, VBA
- How to use Excel color cell if formula
- What is the VBA code to select last sheet in workbook
- How to count names in Excel: formula, using COUNTIF
- How to use an IF statement to add 1 to total
- How to display multiple columns in a validation list
- How to insert file path in excel: cell, sheet
- Convert numbers to words in Excel: without VBA, formula
- How to copy a Macro into a blank cell
- How many IF statements can you nest in Excel
- How to apply a function to multiple sheets on Excel
- How to perform a partial cell match in Excel?
- How to change author name in Excel: spreadsheet, VBA
- How to insert GIF in Excel: 365, sheet, VBA
- How to take screenshot in Excel: shortcut, sheet, cell
- How to enable VBA in Excel: Mac, Office 365
- Copy data from one excel sheet to another: automatically
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Fix arrows keys in Excel: Scroll lock, alternative method