+ Reply to Thread
Results 1 to 7 of 7

Copy, paste special - values only. But keep groups on rows!

  1. #1
    Registered User
    Join Date
    02-23-2010
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Copy, paste special - values only. But keep groups on rows!

    Dear All,

    I am sure this is an easy one, but it was driving my manager crazy and he can't get it to work.

    We have a large spreadsheet that is grouped on the rows so we can see summaries of each section

    However the sheet is full of OLE's so we need to copy and paste special as values only so it doesn't bring the formulae through too.

    However even if we copy the format too it won't keep the groupings at the side only along the top on the columns.

    Please can someone help as it is only a simple thing but will save so much time

    Thanks

    Emmaly
    Last edited by Emmaly; 12-09-2011 at 05:23 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,118

    Re: Copy, paste special - values only. But keep groups on rows!

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-23-2010
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Copy, paste special - values only. But keep groups on rows!

    here is a sample of the data with nonsense numbers in it. We want to copy and paste it as values only but also keep the grouping on the rows. If i do paste special values only and then do formats too it only keeps the grouping along the top. Rather frustrating!Sample.xlsx

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,118

    Re: Copy, paste special - values only. But keep groups on rows!

    This seemed to work for me though, to be honest, there were only values and no formulae.

    First, expand all the groups.
    Select Cell A1
    Press Ctrl-Shift_End to select all the cells
    Press Copy
    Press Paste Special | Values
    Press Paste Special | Formats

    Done.

    I can still expand the groups down and across so, hopefully, you can too.


    Regards

  5. #5
    Registered User
    Join Date
    02-23-2010
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Copy, paste special - values only. But keep groups on rows!

    Hi,

    Thanks for the reply. Irritatingly though it doesn't work. I can paste the group along the top fine but if i follow your instructions i lose the grouping at the side.

    Isn't it bonkers, it should work. Logic tells me it should work, but it doesn't. I think i am actually going to go crackers!!!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,118

    Re: Copy, paste special - values only. But keep groups on rows!

    Ah, I think I may have misunderstood. I thought you were copying the values in place.

    OK, why not just copy the sheet, then copy and paste special values & formats on the new sheet.

    Then use the copy to circulate, or whatever.


    Regards

  7. #7
    Registered User
    Join Date
    02-23-2010
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Copy, paste special - values only. But keep groups on rows!

    Ah ha brilliant, i knew it would be something simple. Thank you!!! Happy Christmas

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1