Macro to "pick up/copy" selected value and paste this into a cell otherwhere

[blaksnm]

Hey Guys
In my excel table - database- the column A:A is allocated to a main-sort-index see example:

Column A
<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=203><COLGROUP><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" width=203><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 152pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=18 width=203>01 [A] Substructure Skid Base AFT/FRW</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=18>02 [A] Drillflor Assembly</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=18>03 Mud Shaker Module</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=18>04 [C] Mud Mix Module</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=18>05 [D] Drilling Power Module</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=18>06 [E] HP Mud Pump Module</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=18>07 [E] Bulk Tank Module</TD></TR></TBODY></TABLE>
... and further on

By filtering on this indexes, my sheet does some subsummaries etc somewhere in row 500
In order to make look.up-function otherwhere in row 500, I need to copy the elected value(s) - which mostly is one single selection /value - and paste this value into cell AH500 for other actions/purposes.
If the selection is multiple - this should be reflected in the pasted value.

Is there a command that "picks up" the elected filtervalue so that I can paste it as described.

I think I can make a macro that copy the filtered index-list - then remove dublicates and then copy/paste the remaining value, but is there a "smarter" way to do it?

 

[Jerry Sullivan]

Hi Snoopy,

Would you be filtering items by checking them individually and/or using Text Filters... to apply a Text Filter like Contains "Mud" or Ends With "Module"?

If you filtered for Contains "Mud", would you want the displayed result to be:
"=*Mud*"
or to list the 3 items that meet that criteria?

Both method of filtering use the Filters.Criteria1 and .Criteria2 Properties, but handling all the different scenarios is not simple. Somebody has probably done this somewhere...you might be able to find some code through a search.

If you limit the type of filters and number of items selected to 1-2 then this UDF posted to Ozgrid might work or get you started.

http://www.ozgrid.com/VBA/autofilter-criteria.htm

This code assigns Criteria1 and Criteria2 to String datatypes, so it won't handle an Array of items selected using the Filter's checkboxes. The code could be modifiied to use Variants instead and step through that Array to display selected items.

 

[blaksnm]

Not quite so ...
I want to insert a value/expression in one particular cell (ex in A1)
then i want to delete all matching cellevalues in the worksheet or in the range (ex B1:AA1000)

 

[Jerry Sullivan]

Not quite so ...
I want to insert a value/expression in one particular cell (ex in A1)
then i want to delete all matching cellevalues in the worksheet or in the range (ex B1:AA1000)

I'm sorry but I don't understand the connection between your original post's question and that comment.

 

[blaksnm]

Sorry
wrong reply

this is what is about:

I filter one item from list in A:A
The same value should be copied into celle K500 (celle named "DPRev")

 

[Jerry Sullivan]

The UDF at the link should prove helpful in doing that.

Good luck!

 

[blaksnm]

Thanks (hehe) - that was quite a help .....