0

I have a workbook that I created using Excel 2007. In Excel 2007 I can copy one of the worksheets to a different workbook with no problems. However, using Excel 2010 I'm getting odd behavior. I open the workbook, create a new blank workbook, then right-click the source tab in the original workbook and click copy to Book1 ("Book1" being the name of the newly created workbook). The sheet gets copied to Book1, but I'm losing two columns on the right side of the page. In other words, when I do a print preview, two columns get cut off that were within the printing limits of the original sheet. I've checked font size, font style, column widths, page size, margins, and page scaling. Everthing seems to be the same as the original sheet, but when I view the two sheets side by side (horizontally) it is obvious (by visual inspection using the ruler) that the columns of the new sheet are taking up more width than in the original, even though the columns are the same pixel width and the scale factor on both is 100%.

It's worth noting that when I have this problem, the numbers and letters that identify the rows and columns of the sheet (e.g. 1, 2, 3 and A, B, C) appear larger than in the original sheet. Not sure if this is related to my problem or just coincidence.

Note that in Excel 2007 I can copy the sheet to a new workbook and all is fine. Additionally, in Excel 2010 I can copy the worksheet using the "Copy to new workbook" option (i.e. creating the workbook and performing the copy in one step) and it works without this odd behavior. I only have the problem when using Excel 2010, and first creating a new workbook, then (in a separate step) performing the copy to new workbook.

Does anyone know what could be going on here?

  • What file format are you saving the workbook in? XLS or XLSX? – Richie086 Feb 26 '14 at 0:32
  • @Richie086 The new workbook is xlsx and the original is in xlsm. However, the new workbook doesn't have to be saved...I just create it and copy the worksheet (without saving the new workbook). – neizan Feb 26 '14 at 7:13
1

After more frustration and searching on this problem, I think I've come up with the answer. As it turns out, the calculation of column widths in Excel is not simple, and the appearance of columns can depend on many factors and can vary from system to system. One such factor (and maybe the primary one) is the normal font style and size settings. If these are different in another computer, then the columns will automatically adjust to accommodate the normal font.

As it turns out this was the case for my problem. My normal font is Calibri 8 pt, whereas the other machine has the default setting of Calibri 11 pt. The difference in size was causing the problem. I changed the other machine's setting to 8 pt, and the sheet copied over without the size differences (i.e. everything that fit on the original sheet still fit in the new sheet). Note that when I was first trying to debug this issue, I checked the font style and size in the sheet itself, and they matched the original sheet. It's the normal font settings that had to be changed (under "Options").

I found my answer at WindowsSecrets.com and on the MS site. My explanation above was pretty brief, so I recommend taking a look at these pages if you're experiencing similar issues. Hope this helps somebody else.

0

To avoid this problem and set a default: In Excel, click the round button in the top-left corner, and then choose Excel Options. Click on Advanced on the left, and go down to "Cut, Copy, and Paste". Checkmark the box for "Show Paste Options Buttons".

Then, when you paste, use the Paste Options button as described at http://support.microsoft.com/kb/291358 , with the "Keep Source Formatting" choice.

  • Does this work if I'm copying a sheet to another workbook? I'm not creating a new sheet and copying the contents of a sheet and pasting the contents. I'm right-clicking the sheet tab in the original wkbk and copying it to a different wkbk. If the problem can be avoided, even when copying the sheet (not the cells), then please elaborate on how to do it. Are the paste options buttons different than paste special? – neizan Feb 28 '14 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.