• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Paste Special to another spreadsheet

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
Patrick O'Dea
Asked:
Patrick O'Dea
  • 13
  • 11
1 Solution
 
redmondbCommented:
Hi, 21Dewsbury.

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

Thanks,
Brian.
0
 
Patrick O'DeaAuthor Commented:
Thanks Brian,

See attached which I what I get when I paste to another book.
ExcelPastetmp.JPG
0
 
redmondbCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Patrick O'DeaAuthor Commented:
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
 
redmondbCommented:
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
 
Patrick O'DeaAuthor Commented:
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
 
redmondbCommented:
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
 
Patrick O'DeaAuthor Commented:
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
 
redmondbCommented:
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
 
Patrick O'DeaAuthor Commented:
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
 
redmondbCommented:
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
 
Patrick O'DeaAuthor Commented:
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
 
redmondbCommented:
I've stopped the close as I think 21Dewsbury meant to pick one of my posts as the answer.
0
 
redmondbCommented:
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
 
Patrick O'DeaAuthor Commented:
Thanks for everything!
0
 
redmondbCommented:
Thanks, 21Dewsbury.
0
 
Patrick O'DeaAuthor Commented:
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
 
redmondbCommented:
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
 
Patrick O'DeaAuthor Commented:
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
 
redmondbCommented:
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
 
Patrick O'DeaAuthor Commented:
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
 
redmondbCommented:
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
 
Patrick O'DeaAuthor Commented:
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
 
redmondbCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now