-
1 Attachment(s)
Some notes in support of this Forum Question
https://eileenslounge.com/viewtopic.php?f=27&t=34116
Question from the web:
Question
I have range A1:B6 with headers and I have filtered on column B with specific text and this results in three results B3 / B4 / B6
And in another range M11:M13 I have three different values (Tanta, Luxor, Aswan)
https://i.postimg.cc/rwCFDmmm/Yassse...e-in-excel.jpg
When trying to copy the range M11:M13 and then select the visible cells in B2:B6 then using paste , I got error message
https://i.postimg.cc/pLtLV8P4/Yassse...xcel-Error.jpg
_.________
Typical Answer
Excel cannot do this. If you want to paste a contiguous range into the visible cells of another range, you have to do it cell by cell.
_.______
Hmm…
This was one working solution from me
Code:
Option Explicit
Sub PasteIntoFilturdRange() ' http://www.eileenslounge.com/viewtopic.php?f=27&t=34116&p=264507#p264507
Range("B3:B6").SpecialCells(xlCellTypeVisible).Copy
Paste Destination:=Range("L11")
Range("K10:K13").Value = "=IF(ISERROR(VLOOKUP(B3,L11:M$13,2,FALSE)),B3,VLOOKUP(B3,L11:M$13,2,FALSE))"
Range("K10:K13").Copy
Range("B3:B6").PasteSpecial Paste:=xlPasteValues
End Sub
(Note that it is important to have no $ on the L in range L11:M$13. This is because we want to get the next Alex, not always the same first one)
-
1 Attachment(s)
-
1 Attachment(s)
_......a quick test, Following on from the last post
We have an add-in offering from new member , Andreas Killer ,
https://www.excelfox.com/forum/showt...5160#post25160
Testing in Excel 2007
I downloaded the .zip file, Paste into AutoFilter.zip , somewhere arbitrary, and copied the contents from that .zip folder into the same place. ( Just my way of "unzipping" things ).
https://i.postimg.cc/gcxz4HzF/Downla...Excel-2007.jpg
https://i.postimg.cc/gcxz4HzF/Downla...Excel-2007.jpg
I simply opened Paste into AutoFilter.xlam, and there was the promised bit in the ribbon
https://i.postimg.cc/NGZgn2Fy/The-bit-in-Excel-2007.jpg
https://i.postimg.cc/NGZgn2Fy/The-bit-in-Excel-2007.jpg
I select and copy the bit I want to paste into a filtered range
https://i.postimg.cc/SRBtGhys/Copy-a...ered-range.jpg
https://i.postimg.cc/SRBtGhys/Copy-a...ered-range.jpg
I select the filtered range
https://i.postimg.cc/BbLVD6fk/Select...ered-range.jpg
https://i.postimg.cc/BbLVD6fk/Select...ered-range.jpg
Then I select the first icon thingy from Andreas , ( step 3 in the next screenshot )
https://i.postimg.cc/q7wSHSGb/select...eas-step-3.jpg
https://i.postimg.cc/q7wSHSGb/select...eas-step-3.jpg
Bingo!
https://i.postimg.cc/1tYdfF2D/Bingo.jpg
https://i.postimg.cc/1tYdfF2D/Bingo.jpg
Just to check, I took a look at the unfiltered range, and the results are as expected
https://i.postimg.cc/XJvhjLHV/Just-t...no-filter.jpg]
https://i.postimg.cc/VNmpTmX0/Just-t...-no-filter.jpg
I did that on the 4th SchlappenBier on a Friday night , so any sane and sober person should be able to do it
https://i.postimg.cc/rwVZqgYK/Third-...itag-Abend.jpg
:)
On Excel 2010 and above I have had no joy yet. Probably as Microsoft fucked everything up from sometme around 2010 onwards.
I have to be "off air" for a few days, I will take a look later, next week, or wait until Andreas has done a few more posts. …
