Solved

Paste Special to another spreadsheet

Posted on 2013-01-29
24
324 Views
Last Modified: 2013-02-01
Hi,

I have a spreadsheet with formulas and formatting.

How can I copy and paste special to a DIFFERENT workbook.
The paste special behaves totally differently when using a different workbook.
I had thought that this would be very simple!

(Excel)
0
Comment
Question by:Patrick O'Dea
  • 13
  • 11
24 Comments
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, 21Dewsbury.

What different behaviour are you seeing? Off the top of my head, I can't think of anything.

Thanks,
Brian.
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
Thanks Brian,

See attached which I what I get when I paste to another book.
ExcelPastetmp.JPG
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, 21Dewsbury.

IS it possible that you're posting from one instance of Excel to another?

For example, you started excel from, say, a Desktop icon. You then double-clicked on the icon a second time thus giving you a second instance. When you try and do a PasteSpecial from one instance to the other, lots of things get lost, hence the dialogue you're seeing.

The only way around this is to use only a single instance.

If this doesn't describe your situation, please post redacted copies of the source and destination files.

Thanks,
Brian.
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
Brian,

See attached workbook - very simple one!

1. I copied the only line in this sheet.
2. I then closed Excel
3. I then opened excel and was presented with a new blank sheet
4. WHen I paste special I get the screen attached in my previous post.
EETUE1.xlsx
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
21Dewsbury,

Thanks. What you describe is pasting from one instance to another. Even though they aren't simultaneously open, it's still two separate instances.

Why are you recycling Excel in the middle of the Copy/PasteSpecial, or is this just something that happened?

Edit: Given that you are using PasteSpecial, it's probably not what you want, but I was pleasantly surprised that selecting "XML Spreadsheet" copies formulas and formats (but unfortunately not Conditional Formatting and, I suspect, a lot of other things).

Thanks,
Brian.
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
Brian ,

I am sure you are right but I am confused!

Workbook "A" has over 20 sheets.

So I create a new workbook "B" and then copy ONE sheet from Workbook A and paste it into Workbook B.

This seems a reasonably thing to do (?) and I am wondering how I achieve it.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
21Dewsbury,

Yes, it's entirely reasonable! And I now understand why you're doing what you're doing...

Edit: I see from your profile that you're very much an Access user. You're used to an entry on the Task Bar for every open file, whereas, by default, Excel has a single entry regardless of the number of files open. Depending on your version of Excel, we can change Excel's default behaviour to be the same as Access - is that what you want?

Edit: You mention copying sheets. So far you just described copying data from one sheet to another one. There's a different process if you want to copy the whole sheet to another workbook. Is that what you want to do?

Oh, what version of Excel are you using?

Regards,
Brian.
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
Brian,

Thanks for your persistence!

In response;

1. I am actually providing a solution to a third party.  So I would rather NOT change the behavior of their Excel.

2. I am using Excel 2010 (not too sure about my third party at the moment).

3. Yes,  I want to copy an ENTIRE sheet to a new workbook.  (Important to note that I want to copy "values" and "formats" but NOT formulas.  This is because the formulas refer to data that will not be in the newly created workbook).
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
21Dewsbury,

1. I am actually providing a solution to a third party.  So I would rather NOT change the behavior of their Excel.
OK, but I've come this far, so please see the screen-shot of the Excel Options dialogue below. (Alt-F-T to display the dialogue.).2. I am using Excel 2010 (not too sure about my third party at the moment).
Thanks. All of the screen-shots and instructions are from Excel 2010.

3. Yes,  I want to copy an ENTIRE sheet to a new workbook...but NOT formulas.
This is going to be two completely separate operations - first copy the sheet, then replace the formulas by their values.

(A) Copy a Sheet.
(i) Right-click on the tab showing the sheets name.
(ii) From the menu displayed, select "Move or Copy...",
(iii) The following dialogue is displayed....(iv) Click on the drop-down and select "(new book)". (You may have to scroll up to see it.)
(v) Tick the "Create a copy" checkbox. (Vitally important - the default is to move the sheet.)
(vi) Click on "OK".

(A) Replace Formulas by Values.
(i) Turn off filters - on the Ribbon's menu bar select "Home", click on "Sort and Filter" and click on "Clear". (It may be greyed out - that shows that no filter is active.)
(ii) Select all of the sheet's cells by clicking on the grey box to the left of the column letters and above the row numbers....(iii) Display hidden rows/columns by right-clicking anywhere in the row of column letters and clicking on "Unhide". Then right-click in the column of row numbers and click on "Unhide".
(iii) Copy the cells using your usual method (Ctrl-C, Ctrl-Ins or click on the "Copy" button on the Ribbon's Home tab).
(iv) Click on the downward pointing arrow beneath the "Paste" button on the Ribbon's Home tab.
(v) On the menu displayed, click on "Paste Special...". (If no menu appears, you probably clicked too high and did a normal copy - go back to step (iv)!)
(vi) The "Paste Special" dialogue (see below) should be displayed. Select the "Values" radio-button and click on "OK"..Regards,
Brian.
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
Brian,

Thank your for your comprehensive answer.  It worked very well.
There was one issue which I overcame; there are some merged cells in my original sheet and  Excel indicated that "The Operation Requires The Merged CElls to be identically sized".

The merged cells were in the top few rows and I did not have any formulas there.

Before, I close the call, I presume there is no simpler way of over coming the "merged" cell issue.  

Thanks again for your patience and persistence.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
21Dewsbury,

Darn merged cells! They're largely loathed for issues like this - I would have turned them off in your place.

This is all well and good for a one-off - but would you prefer a macro to do all this?

Regards,
Brian.
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for 21Dewsbury's comment #a38834874

for the following reason:

Brian,

Thanks for all your assistance.
It has been very valuable..

It almost looks as if I was "teasing" you with the "merge cell" issue!

And yes, I am in the process of writing a simple macro.

(I am still somewhat suprised by how tricky this matter turned out to be).

Thanks again,
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:redmondb
Comment Utility
I've stopped the close as I think 21Dewsbury meant to pick one of my posts as the answer.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
21Dewsbury,

Please see my previous post .

I am still somewhat suprised by how tricky this matter turned out to be.
That's why I suggested a macro - the replacement of the formulas by their values is a single line of code.

Regards,
Brian.
0
 

Author Closing Comment

by:Patrick O'Dea
Comment Utility
Thanks for everything!
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, 21Dewsbury.
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
And finally,   my parents did not really give me the name 21Dewsbury ......

I must try rectify as my choice of name which was not really intended to stick !
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
I must try rectify as my choice of name which was not really intended to stick !
Ouch - officially, EE doesn't permit that. (Of course, it may be different for paying customers!)
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
And finally ... again...

I tweaked to solution about and found a simpler way.

1. Create a new sheet in the workbook (call it sheet99).
2. Copy master sheet and paste values into sheet99
3. Now paste formats in sheet99.
4. Next right click on sheetname (sheet99) and choose "Move or Copy"
5. Choose "new book" and VOILA!

The values, format (including merged cells) all copy across fine.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, 21Dewsbury.

However, isn't this my solution (in a different sequence), with an extra step, without including page set-ups, conditional formatting, column widths etc. and unable to correctly handle hidden rows/columns and/or filters?!
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
Brian,

My solution (a modified version of yours) does include formatting , column widths etc.

In particular, my solution handles the "merged cell" issue without any additional processing.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
21Dewsbury,

Column width and Conditional Formatting (not Formatting!) were very careless of me - apologies - but having hidden rows/columns and active filters is going to do funny (and not so funny) things.

Also, I just took your word for problems with merged cells (give a dog a bad name), but I can't recreate your problem while using either approach. Could you post a copy of the offending header records, please?

Thanks,
Brian.
0
 

Author Comment

by:Patrick O'Dea
Comment Utility
Brian,

...I just had a little debate with myself.

It's late night here and I have had a glass of wine or two.

The result of the debate is that I will respond tomorrow to your comments - my concentration might not be up to it tonight!
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
21Dewsbury,

Whenever is good - you're helping me out, so thanks again!

(Unfortunately I've just finished my wine - a cheap, but pleasant Tempranillo.)

Regards,
Brian.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now