Try this. It worked for me. Check that the workbooks are of same type. i.e. both are .xlxs workbooks. If not convert to same format. Close excel. Restart excel. Open both workbooks from inside the file dialogue box under Recent Documents. That is workbooks should be opened from same instance of excel opening. Select the worksheet to be copied. Right click. Click on move or copy tab. Click on Create a copy. Select the workbook to which worksheet is to be opened from To Book and choose location from "before sheet". That's it. Show
Problem seems to be that a workbook originating under Excel2003 or before has a maximum rows of 65535, whereas Excel2010 or after workbooks have the 1m-odd limit if created under Excel2010. Solution when trying to copy a worksheet to an 'old' workbook (even if now xlsx) is to save the new worksheet as .xls, close it, and then re-open the new version (assuming you have fewer than 65535 rows, of course!) when the usual move/copy will work - no need to close down Excel. Haven't tried upgrading target workbook to avoid this problem in the future (worried about damaging it), but presume that using 'Save As' and using .xlsx format, then unloading and re-loading might just cure the overall problem. However, there may be a problem with NORMAL.XLT (I think) which is the default template and may determine the default limit, so you may need to re-build this too.
Excel tab moving resolved Thanks for the info below, it helped me a lot. Try this. It worked for me. Check that the workbooks are of same type. i.e. both are .xlxs workbooks. If not convert to same format. Close excel. Restart excel. Open both workbooks from inside the file dialogue box under Recent Documents. That is workbooks should be opened from same instance of excel opening. Select the worksheet to be copied. Right click. Click on move or copy tab. Click on Create a copy. Select the workbook to which worksheet is to be opened from To Book and choose location from "before sheet". That's it. "Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook." สาเหตุของที่มาของปัญหานี้คือ ไฟล์ที่เราจะ copy หรือ move sheet ข้อมูลไฟล์ต้นทางเป็น .xlsx และไฟล์ปลายทางเป็นไฟล์เวอร์ชั่นเก่า .xls จนไม่สามารถ copy หรือ move ได้ เพราะว่าขนาดของ sheet ไม่เท่ากัน .xlsx มีแถวทั้งหมด 1,048,576 แถว แต่ว่า .xls มีแค่ 65536 แถวเท่านั้น วิธีแก้ง่ายๆ คือแปลงไฟล์ .xls เป็น .xlsx ซะด้วยการ save as... แล้วปิดและเปิดไฟล์ใหม่ แล้วลอง copy หรือ move sheet อีกทีครับ ก็หวังว่าบทความนี้จะเป็นแนวทางช่วยแก้ปัญหาการ copy หรือ move sheet ข้อมูลใน excel ของทุกท่านได้ ถ้าผิดพลาดประการไดก็ต้องขออภัยไว้ ณ ที่นี้ด้วย เพราะผมเขียนมาจากประสบการณ์ที่เคยเจอมา และสามารถของปัญหาของผมละของทุกท่านอาจจะไม่ตรงกันซะทีเดียวครับ There may be many situations when you need to create a new spreadsheet based on an existing one or move a tab from one Excel file to another. For example, you may want to back up an important worksheet or create several copies of the same sheet for testing purposes. Luckily, there exist a few easy and quick ways to duplicate sheets in Excel. Excel has three inbuilt routes to duplicate worksheets. Depending on your preferred working technique, you can use the ribbon, mouse or keyboard. Method 1. Copy Excel sheet by draggingUsually, you drag-and-drop to move something from one place to another. But this method also works for copying tabs and is, in truth, the fastest way to copy a sheet in Excel. Simply, click on the sheet tab that you want to copy, hold the Ctrl key and drag the tab where you want it: Method 2. Duplicate a sheet by right-clickingHere's another way to duplicate a sheet in Excel that is just as easy:
For instance, that's how you can make a copy of Sheet1 and place it before Sheet3: Method 3. Copy a tab in Excel using the ribbonThe ribbon contains all the features available in Excel, you just need to know where to look :) To copy a sheet, go to the Home tab > Cells group, click Format, and then click Move or Copy Sheet: The Move or Copy dialog box appears, and you follow the same steps as described . How to copy Excel sheet to another workbookThe common way to copy a sheet to another workbook is this:
Note. Excel displays only the open workbooks in the To book drop-down list, so be sure to open the destination file before copying. Apart from this traditional route, there is one more way to do the same thing. Guess what, simply by dragging a sheet from one Excel file to another! How to copy a sheet to another workbook by draggingIf Excel allows duplicating a sheet within the same workbook by dragging it, why not try to use this method for copying a sheet to a different workbook? We just need to view both files at the same time. Here's how:
Awesome, isn't it? The next time you need to copy information from one Excel file to another, forget about copy/pasting and drag the sheet. How to copy multiple sheets in ExcelAll the techniques that work for duplicating a single sheet can be used to copy multiple sheets. The key thing is to have several worksheets selected. Here's how you can do this:
With multiple worksheets selected, do one of the following to copy them:
To copy multiple sheets to another workbook, select them as described above, and then follow one of . How to copy Excel sheet with formulasGenerally, you copy a sheet containing formulas like any other sheet. The formula references adjust automatically in a way that works just fine in most cases. If a sheet with formulas is copied within the same workbook, the formulas will refer to copied sheet unless you use external cell references that point to another sheet or workbook. For example: If you copy a sheet with formulas to another workbook, the formula references behave in this way:
For the copied formulas to refer to the sheet with the same name in the destination workbook, you can simply remove the original workbook name by using the feature of Excel. Here's what you need to do:
As the result, the formula in the copied sheet will change from
To
Copy information from one Excel sheet to another using formulaIn case you don't want to copy the entire sheet, but only some part of it, select the range of interest and press Ctrl + C to copy it. Then switch to another sheet, select the upper-left cell of the destination range and press Ctrl + V to paste the copied range. For the copied data to update automatically as soon as the original data changes, you can copy information from one Excel sheet to another by using formulas. For example, to copy data from cell A1 in Sheet1 to cell B1 in Sheet2, enter this formula in B1:
To copy information from another Excel file, include the workbook name in square brackets:
If needed, drag the formula down or to the right to pull data from other cells. Tip. To get a right, type the equality sign (=), then switch to another workbook, click the desired cell, and press Enter. Excel will insert the reference for you automatically. To copy data from several sheets into one, please see How to merge multiple sheets in Excel. How to duplicate an Excel sheet using VBAIf you often need to copy information from one Excel sheet to another, macros can save you a lot of time. Here are just a few examples of tasks that can be performed automatically: For more examples, please check out How to duplicate a sheet in Excel with VBA. How to move sheets in ExcelMoving sheets in Excel is even easier than copying and can be done with the already familiar techniques. Move a sheet by dragging itTo move a sheet(s) in Excel, you simply select one or more tabs and drag them to a new location. For example, here's how you can move Sheet1 and Sheet3 to the end of the workbook: To move a sheet to another workbook, place the workbooks side-by-side (View tab > View Side by Side) and then drag the sheet from one file to another. Move a sheet via Move or Copy dialogOpen the Move or Copy dialog box either by right-clicking the sheet tab and selecting Move or Copy from the context menu or by clicking Home tab > Format > Move or Copy Sheet. Then, do one of the following:
Note. If the destination file already contains a worksheet with the same name, Excel will add a number in brackets to the end of the moved sheet's name. For example, Sheet 1 will become Sheet 1 (2). Workbook Manager - quick way to move and copy sheets in ExcelIf you often copy or move sheets from one Excel file to another, our Workbook Manager can make your life a lot easier. Run the tool by clicking the Workbook Manager button on the Ablebits Data tab in the Manage group: Now, you have a tree-view of all open workbooks and can easily drag sheets between different files:
As you do this, the sheets in your workbooks are rearranged accordingly without you having to switch back and forth from one file to another. You are welcome to download a trial version of our Ultimate Suite to try this and 70+ more awesome tools for Excel. Move or copy sheet in Excel not workingUsually Microsoft Excel copies and moves sheets without a hitch. If a certain worksheet refuses to be copied or moved, this may be because of the following reasons. 1. Excel tableYou won't be able to move or copy a group of sheets if one of those sheets contains an Excel table (vs. a range). Each such worksheet should be dealt with individually. 2. Protected workbookMoving and copying sheets are not allowed in . To check if the workbook is protected, go to the Review tab > Protect group and have a look at the Protect Workbook button. If the button is highlighted, it means the workbook is protected. Click that button to , and then move sheets. 3. Excel namesWhen copying or moving a sheet from one Excel file to another, you can get an error message that a certain name already exists, meaning the source and target workbooks contain a table or range with the same name. If it is just a single error, click Yes to use the existing version of the name, or No to rename it. In case of multiple errors, it makes sense to review all the names before moving or copying sheets. For this, press Ctrl + F3 to open the list of names in the active workbook - you will be able to edit or delete the names from there. For more information, please see How to define and use names in Excel. That's how you can move or copy a sheet in Excel. I thank you for reading and hope to see you on our blog next week. |