copy and paste special while filter?

[JohnSeito]

Hello,

Do anyone know that if excel can be copy and paste special value when some of the columns are filter? It seems that you can only do copy and paste special value when unfilter everything column.
thx

 

[JohnSeito]

Hi Datsmart,

this is what I did. I only use this as an example.

I have column A cell (a,1) cell (a,2) cell (a,3) etc as 1, 2, 3, 4, etc all the way to 20.
I have column B cell (a,1) cell (a,2) cell (a,3) etc as =a1, =a2, =a3 etc all the way to 20

I then filter column a as greater than 6, so now all the number we see are all greater than 6.

then in column C I put formula =a7, =a8, =a9 etc because this is after the filter. I only do this so I want to see that if it doesn't work with what is in column B maybe it will work here.

I then do as what you suggest which is

EDIT|Goto|Special > Select > visible_cells_only
Ctrl-c to copy
Right click location where you want to paste
Paste special | Values

this doesn't work. what I want to do is after filter, I want to select the column or the whole sheet and copy, then paste special value so that the formula is no longer there. I do not want to move the data around. thx

 

[Datsmart]

this doesn't work. what I want to do is after filter, I want to select the column or the whole sheet and copy, then paste special value so that the formula is no longer there. I do not want to move the data around.

Doing the above which I have quoted from your last post is not the same as what has been suggested.
The suggestions you have received are:

Select the Filtered table, clicked Edit, then Goto|Special
Click "visible_cells_only", OK
Ctrl-c to copy
Right click location where you want to paste
Paste special | Values

You must "Select the Table" first, then "Select Visible Cells Only".
Selecting a Column or Whole Sheet will do just that, copy everything, not just the visible cells like was first suggested by Yogi.

 

[JohnSeito]

how did you select the table?

this is what i did, select the column or the whole sheet, then edit, goto, special, select visable cell only, ctr C, paste special value,

it gives me an error, the command cannot be use on multiple selection.

 

[hardeep.kanwar]

how did you select the table?

this is what i did, select the column or the whole sheet, then edit, goto, special, select visable cell only, ctr C, paste special value,

it gives me an error, the command cannot be use on multiple selection.

I Try your Example It works fine for me

First Sort the Data in Ascending order Then Filter Column A and Put the Function in column C.

Now Select the Range (WITHOUT SELECT THE HEADERS I.E COLUMN A1,B1,C1)

now copy,then Paste Special
press OK


Hardeep kanwar

 

[Datsmart]

You can select the Table by clicking the top left cell and dragging your mouse to the bottom right corner.
If you have a very large table where an extended mouse drag is needed, it is quicker to select any cell in the table and hit Ctrl-Shift-*. This selects the congruent cells of a range. This assumes your table has empty cells between it and any other data on that worksheet.

 

[JohnSeito]

hardeep.kanwar

Hey I didn't want to change the data as it is for example as you said sort it in descending order etc. I want to just copied and paste special value and so those formulas are gone, the main point of not doing un filter is because so I get to do last task and finish what i am doing in shorter steps, even if one less step that is still good.

So how did you get it to work?

datsmart,

I know how to select a table, a row, column etc. Just that without un filtering and do copied paste special value doesn't work.

 

[JohnSeito]

bump

 

[JohnSeito]

Hey,

The reason I want to copied paste special value and de-formula the unfilter cell is because I format and moving datas around and doing this so frequently that if I was to unfilter to de-formula it would be time consuming and losing my place when doing so. If I could just filter do the formula then copy paste special value while not unfilter this will be easier and faster.


Could someone tell me if this could be done.

thx

 

[JohnSeito]

bump

 

[jagan6663]

Hello,

Do anyone know that if excel can be copy and paste special value when some of the columns are filter? It seems that you can only do copy and paste special value when unfilter everything column.
thx

Hi,

Goto Formulas tab, -> "Calculation Options", ->Manual.

Then you remove the filter and check the values (I am sure values will not change) then select the column and use paste special values.

Once you done, don't forget to change the calculation option as automatic.