Copy from one cell to a selection, don't copy if cell(s) in selection contain "x" or "y"

Hi Jay,
_ you have explained quite well what you want.
_ But for specific help here with getting a code for you, you would need to give us some more detail and test data to work on , reduced greatly in amount . - Macros tend to repeat or loop and do stuff. So if there is some logic / pattern to your requirement, then we only need to see a small selection of data, and any code we do for you would normally work then with little or no modification on much bigger files.
_ So We need to see what you have initially before anything is done ( again with reduced test data )
_ we call this typically the “Before” or “Befores”). But then importantly we then want to see hand filled result, which is what everything should then look like after running of the macro. We would call this the “After or Afters.
_ We need a clear picture ( Not IMAGES ) of what you have before and what you want after with reduced test data.

. So simply provide a clear before and after ... ( For example a "Before" and "After" sheet in an Excel File )

To summarise.

. The Before or Befores, should look just as it / they does before running of any macro.
.
. The After or Afters, should be hand filled by you so that it they / looks exactly as you want it to After running of any macro, based on the actual sample data in the Before / or Befores
. Make sure there is just enough data to demonstrate your need. Remember to desensitize the data if necessary, but use the typical Format type and choose your data carefully so that it can be used to test all possible scenarios.
...

Alan

P.s. some ways to provide that info: ( Most people use . 2 )
. 1 ) use the Forum Tools in my signature to produce screen shots we can copy to a spreadsheet ( NO IMAGES ! )
. 2 ) Post Files
To Attach a sample workbook:
View Pic
http://www.excelforum.com/members/da...ch-a-file.html
http://tinyurl.com/oenwprw

. 3 ) - Up left in the Thread editor is a Table icon. Click that, create an appropriately sized table and fill it in. (To get the table icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)

. 4) Only as a very last resort, P.M. me and i will reply with my Email Addressee so you can send me a file
** To PM me, click on my name in the left hand margin when you are logged in, the rest should be obvious.

Hopefully this is what you asked for.

The screenshot just shows the cell to be copied already selected and Control-C'ed and the area to be selected already selected. Then the macro should produce the Area after result.

The cell to be copied may reside in another worksheet of the same workbook. But to my way of thinking, the macro should just grab what's in the Office/Excel clipboard...

Thanks for your time,
Jay

Hmm, the screenshot.jpg didn't seem to get attached to my reply. Here's another try at it.

Oh, it was inline, not attached. My bad.

Hi Jay,

Originally Posted by Xanlithe

Hopefully this is what you asked for....

Yep, the extra info makes the general idea fairly obvious, Thanks

Originally Posted by Xanlithe

..... But to my way of thinking, the macro should just grab what's in the Office/Excel clipboard.....

Hmm , never tried, I am still learning myself.. but had a go.. Not sure what the difference is between Excel or Office there ? **.
When I Googled... The second thing I found was a Thread of mine and snb’s that i had forgotten about!!
http://www.mrexcel.com/forum/excel-q...ml#post3988279

Anyways...
A minor confusion, you initially Post #1 mentioned x and y as exclusion things, but i guess you were meaning “things” by x and y , not x and y in the string text content??
But as i have written the code an initial Array (which within reason can be any size) contains the string contents to be ignored,
arrExc() = Array("OFF", "B", "PREP", "L")
so you can easily modify that. ( An additional check later excludes empty cells )

OK:
The macro then checks that you have selected something. ( Excel then recognises that as “Selection” ) . A check is made to make sure “Selection” is a Spreadsheet Range
And
The macro checks you have a text string in the Clipboard ( Not sure what the difference is between Excel or Office there ? ** )
And Then..
_ .. it does the business!

There are lots of extra unnecessary steps , variables and explaining ' Comments, because i am learning as i go along, but you can easily remove bits step by step , substitute variable values in place of the variable, and remove comments as you wish. But initially IMO it helps to understand what is going on.

So:
This is an example of what the code does. If you copied a cell, say D10 in “sheet3” to the clipboard
Using Excel 2007

Row\Col	D
10	TaxIn

Sheet3

And select this Spreadsheet Range:-

Using Excel 2007

Row\Col	G	H
8	TaxBL
9	OFF	OFF
10	B	TaxBL
11	L	TaxBL

Sheet1

Then run the code and you get this:-

Using Excel 2007

Row\Col	G	H
18	TaxIn
19	OFF	OFF
20	B	TaxIn
21	L	TaxIn

Sheet1

( For now I added an Offset of 10 rows for the output, just so to ease testing. If you remove the .Offset(10, 0) at the end of the code then the output will overwrite the input, as i believe you wish.


I have put the code here:

http://www.excelforum.com/developmen...ml#post4230183

Alan

Hmm, since the copy cell copy is just the text taken from what amounts to be the original DOS clipboard, I don't see how to copy the format... That's why I mentioned the Office/Excel Clipboard. Perhaps there's a way to access that. Using the code:

'3b) Use an object that amougst other things gets text from Clipboard
Dim objClipboard As Object 'Clipboard Object: It has a Funny long name as it is ..
Set objClipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '..not necerssarily anything to do with Excel or FileScripting
'The above two lines is a "Late Binding" approach to making Clipboard stuff available. Useful for Sharing as then the extra refferrence to a Library in the following alternative method must not be done: The alternative "Early Binding" would need a referrence to Microsoft Forms 2.0 Object Library - In VBEditor: --Tools --References --Scroll down and check the box next to Microsoft Forms 2.0 Object Library..and then the next two lines.....
' Dim objClipboard As DataObject
' Set objClipboard = New DataObject
objClipboard.GetFromClipboard ' a necerssary inbetween step. Not quite sure wot it does, maybe sort of preparing the clipboard to give stuff
Dim TextFromClipboard As String
Let TextFromClipboard = objClipboard.GetText

gets us the text only from what is essentially the old DOS clipboard (I think).

Although I seem to remember that there is more than one possible format for the grabbing of info from the clipboard using different parameters.

I think that's why I mentioned the Office/Excel Clipboard. It contains the formatting info. Perhaps combining the text with the formatting in some sort of concatenation?

And perhaps I am over-analysing the situation and you may have a simple solution to the formatting issue.

But if not, my ruminations may point you in the right direction.

Hi Jay
Thanks for the feedback. The code is actually quite simple, and very basic, as i am learning, rambling, ( and making the comments up as it were ! ) as i go along ... Most of the experts here could knock up something better that takes up about 2 lines to do it all!!! ( and takes them probably about 2 seconds to write it!!. )
I will answer all your questions ( If I can ) when i am in me “VBA” periods and post back...

_ While I am here, let me see wot I can answer now....

_ The code as I have written it should not work in your PMW ( I think?? – doesn’t work in mine ). But the fix for that is simple: The current code Sets the ws to a Worksheet in the File in which the macro is in
( the hint in the code is ThisWorkbook ) Here would be one fix

Please Login or Register  to view this content.

This makes it work at “my End”... in my PMW ( luckily i just recently found out what a PMW is!! - )


Note:

_ 1) The file you are working on ( Selecting a range from ) must be open
_2 ) You need to change CopyPasteJay.xlsm to your file name
_3) The file must be selected, or else the “Selection” will be the selected file ( what you are looking at ) ... so maybe this would be Better:

Please Login or Register  to view this content.

_ ........................

_ I always did use very much more descriptive names for variables, as a personal choice. But it seemed to give others ( especially experienced users the hump ( get upset ) ) - so I have as a compromise “cut back” a bit. I think it has no effect whatsoever on speed ( But am guessing a bit there) . r and c were the obvious ones i thought to use if i must use short for row and column and also j and i which I seem to remember was like y and x co-ordinates in my school and Army days.... ( Excel is back to front ( I think?? - like y x coordinates instead of x y ) ) but i digress.... Often i use rws or clms instead or ThisRow, ThatRow.
Yes you must be careful with things like Row, Rows etc, but many pros are careless, often using for example cell as a range variable then a typo to cells can be a disaster and vice versa. Personally i would stick with long descriptive variables names... Also Always use a few capitals when defining a variable, like
Dim LongVariable As Long
Then later in the code type it as longvariable in lower case. Then if you spelt it right ( and have Dim ed it right, VBA will convert it to LongVariable, - So then that is a good check against typos..

_ I will get back with all the rest. Not sure when, In Germany it is late and Tomorrow is a national Holiday. But feel free to ramble, I’ll probably learn stuff as well.. I think just about anything can be done with VBA, - just a question of how and/ or how complex, or if it is easier to modify or re-write a code.. My current code uses Arrays, as you noticed, a personal favourite with me. After a Bit of practice it becomes second nature. People are beginning to catch on to the advantages and are going away from using so much interaction with the Spreadsheet,
http://www.excelforum.com/tips-and-t...of-arrays.html
http://www.excelforum.com/excel-prog...ml#post4199233

- Everytime you interact with the spreadsheet it is like slaming the brakes on.. As would be the case with your background coulors idea, but that can be done also. I will have a think how.

Alan

EDIT actually R and C are "reserved" sometimes in formula stuff, so maybe r and c is bad to use ( but most pros do )

Originally Posted by Xanlithe

... Jeesh. Didn't notice the copy into the PMW. Double Jeesh......

I heard a few other people say that. On you Tube Videos... seems to happen on its own sometimes... But not by me yet.. i only have two codes in my PMW - a sreenshot making tool from JBeaucaire
http://www.excelforum.com/tips-and-t...um-thread.html
and now your code! ( - But I put it there ... )

Originally Posted by Xanlithe

.......
I do have other (really simple) macros that I run from my PMW that do work on the aforementioned work spreadsheet. Such as:

Sub CommentLate()
'
' CommentLate Macro
' Inserts Comment in active cell "Late, called at <time>."
'
' Keyboard Shortcut: Ctrl+Shift+C
'
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:="Jay:" & Chr(10) & "Late, called at " & Format(Now(), "h:mm am/pm") & "."
ActiveCell.Comment.Shape.TextFrame.AutoSize = True
ActiveCell.Select

End Sub
........

You are using Active stuff there, what is typically the Macro recorder throws up.
The code equivalent there for our code would be

Set ws = ActiveSheet ' Alternative , but Worksheet of interest must be Selected ( being looked at ) at the time this code part is gone through

I often used to good effect Windows("_____").ActiveCell, as a sort of trick to get "as many active cells as i wanted" But that really put the back up some pros, so i stopped doing it..
http://www.mrexcel.com/forum/general...hierarchy.html
http://www.mrexcel.com/forum/questio...ml#post3823254

Originally Posted by Xanlithe

..........

Note: I've actually used the clipboard functions as designated in your code in other macros. And it does/did require access to MSForms 2.0. This may have been superseded by Microsoft Forms 14.0 Object Library (when using Office 2010, presumably this would be presented as Microsoft Forms 15.0 Object Library for Office 2013) as I don't see any option for Microsoft Forms 2.0 Object Library any longer.

Please Login or Register  to view this content.

If I do this: ( that is to say go for the “Early binding pair” )

Please Login or Register  to view this content.

the code only works with the reference Microsoft Forms 2.0 Object at "my end". But I am in XL 2007 and have Microsoft Forms 12.0 Object Library. But next time I am on my XL 2010 Computers i will check that. Thanks for that info. I guess Yous noticed the original code as i gave You was using the „Late Binding pair“ which never need any reference. For myself i always use Early binding as Then i get intellisense fort he object . But for sharing I use Late binding so i do not have to tell the OP to use the reference )

( Hope i have those two the right way around ! )


EDIT: I see now do have Microsoft Forms 2.0 Object Library in my XL 2010, but have not tried the code yet there

Hi Jay
Re Copying the Formatting:
Fundamentally the Array method i am using is limiting those sort of things. It would be very interesting how to get that info from the clipboard, i know there are lots of versions there as the line ,
Let ClipFormCnt = CountClipboardFormats()
In the code shows....

But my code only takes in all values in one go and then ( after the text manipulation based on your criteria ) pastes the values out in one go. That is the fundamental nice idea about the Array method. – You only interact a few times with the spreadsheet, doing all the “workings” “internally” as it were, which has the great speed advantages.
But reverting ( at least partially ) to some Spreadsheet interaction, determining and assigning out the Background color and / or simple Copy Pastespecial techniques give infinite variations on how to do it.
I think it is actually very simple, but I am “off” now, but will do it if you do not figure it out in the meantime
Alan

EDIT: One very quick way, definitely not the most efficient ( it would be better to rewrite the whole code and incorporate it somewhere within ) ... would be to just tack this last section on to the code ( Note i still have the .Offset(10, 0) bit

Please Login or Register  to view this content.

Alan

Here full code ( Personal Workbook version )
( – Change
CopyPasteJay.xlsm
to suit the file your selection is in )

http://www.excelforum.com/developmen...ml#post4230461

Hi Jay,
Again with Copying with the Formatting:

Of course, if we choose now to “interact with the spreadsheet” by pasting directly from the clipboard, ( rather than getting stuff out of the clipboard and doing stuff “internally” with that through my Array method ) then we should bear in mind, or remind ourselves of the “obvious” or “profi” or Spreadsheet" way of doing what you want.

Reminding ourselves that you have at the outset all the info you want in the Clipboard , both your text and all formatting. Only Out of interest we are considering how to get that Format info out of the Clipboard. I still do not know how to do that. But I do know that one can use on a Worksheet Range the VBA .PasteSpecial Method
https://msdn.microsoft.com/de-de/lib.../ff839476.aspx
http://www.mrexcel.com/forum/excel-q...ml#post4071766
This Method ( I think ) allows us to select which version of the ( many versions
http://www.mrexcel.com/forum/excel-q...ml#post4043472
) held in the Clipboard we paste out.

Maybe we should go back a bit here, and a small note here is maybe worthwhile on the difference between _1) VBA Worksheet .Paste Method and _2) VBA Worksheet Range .PasteSpecial Method ( and _3 ) VBA Paste Destination:=!! )

_1) VBA Worksheet .Paste Method in its simplest form pastes to the selected range in the specified Worksheet.
https://msdn.microsoft.com/de-de/lib.../ff821951.aspx
This will not work too well for us, as we wish to use the Selection as a Range Object. However by including the optional argument Destination:= Specified Range we can overcome that. This Method tends to guess which version of the Clipboard is pasted out, but will probably be OK for us. (This “guessing” of VBA can catch you out with Formulas for example:
http://www.mrexcel.com/forum/excel-q...ml#post4028956
http://www.mrexcel.com/forum/excel-q...ml#post4045526
_ .... here using
.PasteSpecial Paste:=xlPasteFormulas
Instead saved the day )

So two versions of a code to do your full current requirement, the first Simplified, and the second Explained.

Please Login or Register  to view this content.

_2) VBA Worksheet Range .PasteSpecial Method
Very similar Idea to _1) , but just we are able in addition ( with an optional argument ) to be selective on the version of the Clipboard that we finally Paste ( or rather .PasteSpecial ) out. A small note to remember here, a small distinguishing difference between _1) and _2 ) : That is .Paste is a Worksheet Object Method , and an optional Argument Destination:= allows you to specify a Range ( Or else it “goes to” the selected Range in the specified sheet), whereas .PasteSpecial is a Range Object method, so the Range it “goes” to is that particular Range, ( and an Argument allows you to “pick out” which version of the Clipboard is used )

So a couple of simplified versions of this method to meet your current full requirement

Please Login or Register  to view this content.

_ 3) VBA Copy Destination:=
This ( briefly described ) is a quick one liner that bypasses the clipboard,. Again it “guesses” the Format. It is inappropriate for you as you do not Copy.
Methods _1) and _ 2) can be used in conjunction with an initial .Copy ( and usually are – your initial manual copy before running the code is unusual ) .
VBA .Copy Destination:= must be used with a Copy. ( Yet another of the endless variations for your code could be to take in the Range ( Cell ) to be copied, ( maybe via an Input box )
And then this would be continually used in the code with the VBA Copy Destination:= ).


Note

_a) in all the above 4 codes I do not do the ten row offset thing (.Offset(10, 0) ) – this would not work to give the correct answers as in these codes I simply leave alone cells not meeting your criteria for Pasting. ( In the Array codes, for cells not meeting the criteria, the corresponding element in the Array for Output were filled with the existing Input Array values, giving a full output Array. This had the flexibility of letting you pick where all the output would be finally pasted in one go )

_b) the simplified “Spreadsheet” codes do look very simple. But I do not think anyone living can remember exactly what goes on behind the scenes as it were in using the various Object, Methods, Properties etc... My Basic code type would be worked through very quickly by VBA, and as i mentioned before, every Spreadsheet interaction “slams on the brakes” due to the “hidden” things going on. ( These things themselves are of course complicated codes you just do not see. )

Hope i have not confused the issues too much here. But it has helped me to get it clear in my head, so I will be able to clarify or help you further to understand ( i think ) if need be.

Alan