I want to know that how we can copy the data together when there is a
filter in worksheet.

I am attaching a JPG file for your considration.

there are three coulmn and three rows on which i am working. I gave a
filter to Y In coulmn D and then i want to paste the data of coulmn E
in coulmn C. that can replace all the data on the same place. i.e. i
want Ream,y in the place of Ream, shyam,y in place of shyam and btht,y
in place of btht. I want to copy all the data at the same time.

Note Please take a look on the Picture attached with this Thread.


Thanks in advance


+-------------------------------------------------------------------+
|Filename: Problem.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4481 |
+-------------------------------------------------------------------+

--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile: http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=524178

Hi

One solution for your problem is to use a macro for copying multiple
selections, no promises that it'll work but have a look at:
http://j-walk.com/ss/excel/tips/tip36.htm

Or without using a macro...
I'm making a couple of assumptions he
* that the 3 rows in your example are just that, an example, & you will
potentially have many more rows when using real data.
* that the "y" in column D is short for "yes"/true and therefore the
hidden rows will be "n" or false.

If my above assumptions are correct & you would prefer not to use a
macro, I'd:

1) remove the filter (ie show all or [alt + d + f + s])
2)type this into column E:
=if(d2="y", concatenate(C2&", "&D2),C2)

3) copy down as far as needed
4) copy column E & paste special as values into column C.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=524178

HI,

Thanks for your help

Your macro is not working in Filter...


and the second as you tell to paste data in coulmn c As value then it
will over write the data in coulmn C..


Please tell how it can be solve..

Thanks


--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile: http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=524178

Hi,
I thought you may have problems with the macro not working after I gave
it a quick test but had hoped that someone else would spot the thread &
offer advice on how to overcome this.

"and the second as you tell to paste data in coulmn c As value then
it will over write the data in coulmn C.."

I thought that you wanted the data copied into column C?
The formula in column E,
=if(d2="y", concatenate(C2&", "&D2),*C2*)
means that the value of any cell in column E (before the copy action),
will be the *same as the value in column C *if there is no "y" in
column D.
Therefore when the copying to column C occurs, yes, the data will be
overwritten, but it should be the same data as was there before the
copy.

The only instance I can think of where this won't be true is if there
are blanks in column C. In this case, try using this formula instead:
=if(d2="y", concatenate(C2&", "&D2),if(isblank(C2),"",C2))

(Ooopps, missed a closing bracket in above line - now fixed)

I hope this clarifies my line of thinking for you,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=524178