Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    copy&paste without formulas (excel2003)

    hi all,
    say you have a data sheet with conditional format and you want to do copy and paste operation but without including the conditional format formula,only the cell format, how can you do that.
    tia
    TIA
    dubdub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy&paste without formulas (excel2003)

    Do you mean that you want the pasted cells to be formatted the same way as the original cells looked with conditional formatting applied? That wouldn't be easy, I think. You'd have to create VBA code that loops through the cells and analyzes the format conditions for each cell, compares them to the cell value and determines which formatting to apply.

    If you only need a "picture" of the cells, it's easier: copy the cells, move to the destination, then hold down Shift while selecting the Edit menu. The Paste and Paste Special commands will change to Paste Picture and Paste Picture Link. The result will look exactly like the original cells, but it is a picture, you cannot edit the cells.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: copy&paste without formulas (excel2003)

    If you want to copy the formatting and remove the conditional formattiing equations, you can do it in 2 steps, copy and paste will copy the formatting, then remove the conditional formatting and the only the explicit formatting will remain.

    If you want to copy and convert the explicit formatting to what is active for the conditional formatting then you can try Allen Wyatt's "Excel Tips" which has some code for Removing Conditional Formats, but Not the Effects

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy&paste without formulas (excel2003)

    Hi HansV & Steve,
    what I want is copy a large number of cells 10 columns by 2000 rows in sheet-1, in these cells i have conditional formating equations applied to certain cells,then I want to paste it into sheet-2, in sheet-2 i still want to maintain the conditional format effect but remove the equations.

    Reading both replies it seems i need the macro solution that will remove the equation and keep the effect.
    thanks.
    TIA
    dubdub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy&paste without formulas (excel2003)

    Yes, you will need Alan Wyatt's code. There is no "simple" method to do what you want.

Posting Permissions

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