if cell value in range = "code" then copy paste value to last used row another sheet

[SteveM99]

I have data in a range which I need to copy to another sheet. Cells in my range that need to be copied have the word "CODE" in them (the other cells can be disregarded). I want to copy the cells that have values of "CODE" into the last row of another sheet in the same file. I am having a problem adding the "CODES" cells into the last "new" row as the last new row keeps increasing from the last copy.

Assume I have 6 rows I want the macro to go through as follows:
From data example (D = column letter, number is row)

D1 Header
D2 CODE
D3 Disregard
D4 CODE
D4 CODE
D6 Disregard

To data

Another sheet
last row for example is row 25 in column C
after first copy the last row is now 26
then after the next copy the last row is now 27

I don't know how to combine the look through a range evaluation code and copy to another sheet dynamic last row code:think:

[vcoolio]

Hello Steve,

See if this works for you:-


Sub Test()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
.AutoFilter 4, "Code"
.Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
'.Offset(1).EntireRow.Delete
.AutoFilter
End With

Application.ScreenUpdating = True

End Sub

I've attached a sample workbook to show you what this code does. Click on the button to see it work. The three rows of data with "Code" in Column D should be transferred to Sheet2. If you want the "used" data in Sheet1 to be deleted, in the code above, just remove the apostrophe from in front of this line of code:-


'.Offset(1).EntireRow.Delete

I hope that this helps.

Cheerio,
vcoolio.

[SteveM99]

vcoolio

thank you this seems to work perfectly. can you explain the (3) and (2) parts. A few words on the current region ... is that just defining the range of used cells starting at cell A1?

[BIFanatic]

The just returns a range. try this to understand what it returns (next available empty cell in column A)




Sub test2()

Dim z As Range

Set z = Sheet2.Range("A" & Sheet2.Rows.Count).End(3)(2) 'OR
Set z = Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp)(2)

MsgBox z.Address

End Sub




Instead of over complicating the code and making it difficult to remember you can also use this.




Sub test2()


Application.ScreenUpdating = False

With Sheet1.Range("A1").CurrentRegion
.AutoFilter field:=4, Criteria1:="Code"
.Offset(1, 0).EntireRow.Copy Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Offset(1, 0)
.AutoFilter
End With
Application.ScreenUpdating = True

End Sub

[vcoolio]

Hello Steve,

You're welcome. I'm glad to have been able to assist.

The (3) is the numeric abbreviation for End(xlUp) which has Excel search from the bottom (end or the very last row) of a worksheet column then up (xlUp) to the last row of a data set.
The (2) is the numeric abbreviation for Offset(1) which ensures the next data transfer starts at the next empty row in a data set and hence won't overwrite data already in place.

Thus,

.End(3) (2)

is the same as

.End(xlUp).Offset(1,0) or .End(xlUp).Offset(1) or .End(xlUp)(2)

with the 1 being the row offset and the 0 being the column offset (0 meaning that there is no column offset).

Abbreviations are good for those amongst us who don't like typing too much!They also help to keep codes compact.Its a personal choice/option for any coder.

The CurrentRegion is basically the whole data set beginning, in this case, at cell A1.

@BIFanatic:-


Instead of over complicating the code and making it difficult to remember you can also use this.

For the sake of the OP or whomever else may come by this way, can you explain how, and why you think, the code in post #2 is over complicated. Its far simpler to remember than you think!

Cheerio,
vcoolio.

[BIFanatic]

It is easier for the one who knows it, for others how is End(3)(2) more readable than .End(xlUp).Offset(1, 0)?

[vcoolio]

You still haven't given us your esteemed insights as to why its "over complicated".

95% of posters who come to these forums have no idea what .End(xlUp).Offset(1, 0) means, nor do they care. They may have basic Excel skills but their main interest is having something that works for them. They may have copy/pasted something that they found on the internet which works for them and that's all that matters.
Those who are curious and want to learn more will always ask, just as has the OP of this thread.

Enumeration is a big part of Excel, so, in the meantime, I suggest that, instead of pontificating in a solved thread, you delve further into Excel and study its various and differing aspects.
Help those that you wish to help in any thread in any forum and in your own way using the methods best known to you but leave your ego in the cupboard and do not pontificate.

[SteveM99]

BIFanatic,

Thank for the alternative code and input on what may be easier to comprehend for a beginner like me. I appreciate your help and comments.

[BIFanatic]

"95% of posters who come to these forums have no idea what .End(xlUp).Offset(1, 0) means, nor do they care. They may have basic Excel skills but their main interest is having something that works for them. They may have copy/pasted something that they found on the internet which works for them and that's all that matters."


That is your assumption and not a fact, and that's a derogatory statement to use for people seeking help. The OP had to ask you what this means because he/she didn't understand it, since you solved his problem it is your responsibility to make it easier for him/her in one go and not make them come back again and ask a question.


"Enumeration is a big part..." so just because you wrote (3) and I wrote (xlup) means you know enumerations and I don't? This thread wasn't solved as the OP had to ask you a question against your solution, instead of being irritated you should go back and learn some basics and probably first get equipped with some communication skills and not just search for a complex word in google that you have never used before.

[SteveM99]

Vcoolio, again, thank you for your abbrieviated code version. I typically put a comment right before most codes I enter anyway (your version or BIFanatic's) because I forget what they do and I find myself creating macro files utilizing a pirate method from my other constructs (grabbing codes that do different things from various files I have built). I have gotten better and thanks to this site when I get stumped I get great solutions. I am just too stubborn sometimes and end up google-ing codes until I realize I am not a coder just an old Atari, Vic-20, Commadore-64, Amiga 128, 386 Dx2, Sega Genesis, Playstation, Xbox, gamer who sees the code and a way of feeling I am in the Matrix but I am no Neo.

[vcoolio]

Hi Steve,
Again, you're welcome and thanks for your return comments.
Ha, I too am very familiar with all those brand names. Here's another one: Hanimex. We both must be reasonably(?) old!
Cheers,
vcoolio.

[vcoolio]

@BIFanatic:-

I've been around these types of forums a lot longer than you and I can pretty well assure you that that percentage is reasonably correct.
NO, it is not a derogatory statement. It is fact. Join the real world would you. Do you really think that everyone instantly understands what .End(xlUp).Offset(1, 0) means? NO THEY DON'T! Over the years, I've had to explain hundreds and hundreds of times what it actually means and again it was only to those Posters who were curious and wanted to know and learn a little more.

Now some facts:-
- The OP of this thread accepted and stated that the code supplied in post #2 worked perfectly for him. This meant that the query was solved, no ifs or buts and the OP DID NOT ask a question against my solution. His questions, if you bothered to read them correctly, related directly to him wanting to further understand the workings of the code.
- He then asked ME a couple of questions about the workings of the code, NOT YOU. As the questions were directed at me, it was up to me to reply. You just arrogantly butted in and gave your opinion when it wasn't wanted. Did you even consider, in your post, to politely reply and say something like:-
"Hi Steve, here's another version you could try. Let us know what you think." He didn't ask you the questions.
- You do not have the right to rebuke or judge other people's work and assume that you know better than anyone else. You are still too 'green' to consider yourself an MVP (and no, I'm not one either).

Read your post #4 over a few times. You may hopefully then realise how rude, arrogant, condescending and patronising your words are.



"...so just because you wrote (3) and I wrote (xlup) means you know enumerations and I don't?"

What a pathetically petty statement. Did I actually say that? No, I didn't. I really don't care whether you do or don't. How you use code is your business. I won't judge your work. Nor will I judge that of any other helper on any of these types of forums and I never have. If anything, if you asked for help, I would probably more than likely assist.



"....instead of being irritated you should go back and learn some basics and probably first get equipped with some communication skills and not just search for a complex word in google that you have never used before."


Another pathetically petty statement by you. Unlike you, I am way past the basics and my communication skills are light years in front of yours.
And no, there was no irritation on my part but when it comes to the arrogance displayed by people such as yourself, whether it be here on these types of forums or in my real life outside of these forums, then those people, I consider, need to be put in their place and told their true pedigree.

BTW, English is my native language so I have no need to search Google for any complex words and their meanings. They're always at my disposal. You, on the other hand, perhaps should use Google on a regular basis to search for the meaning of words such as arrogance, patronising, condescending, pontificate, rude........

Just to re-iterate, from my post #5:-


Abbreviations are good for those amongst us who don't like typing too much!They also help to keep codes compact.Its a personal choice/option for any coder.

with the key words being:-


Its a personal choice/option for any coder.
so get over it.

Lastly,
Have you bothered to thank Steve for his return comments in post #8?

[BIFanatic]

It is not up to you to decide who responds to whom, you don't own this thread, again don't be irritated, just accept that you don't know how to resolve a query and probably don't have any social quotient either. Literally nobody cares for your abbreviated codes, most of the users won't understand it. You have been here for ages but your post count is 40, that speaks for itself.

P.S I wish your brain worked as fast as your fingers so you could make it easier for the OP to understand your horrendous code in your first reply. There is no harm in accepting that you don't know how to make things easier for others and you just want to show off that you know something that others don't.

[vcoolio]

So its taken you three days to search Google for a decent word or two. You've just proven how slow your brain is.
What's more, you had to edit your last post because you're too thick to maintain a clear train of thought!

You are a complete and utter fool. You don't have any talent and are a complete waste of time and space.
My reference to how long I've been around doesn't refer to just this forum. Have a look around but also properly read and understand posts before you make your usual ridiculous comments.

You must be extremely thick. You obviously didn't understand a thing that was said in post #12....THE FACTS!!

No,I don't own this thread. The OP doesand, as previously stated, he asked ME the questions in relation to my solution, not you. Can you not get that through your thick skull?Are you really that stupid? What's more, did anyone ask you for your opinion on someone else's work? No they didn't! Not even the OP.
In your private life, do you just butt in to conversations that don't include you just so everyone else can listen to your loud mouthed opinions? Did you not understand that this particular conversation was between the OP and me? There isn't any difference, is there?
Go back to kindergarten and start over and learn some etiquette while you're at it as you obviously have never been taught any manners.You are just plain rude, ignorant, arrogant and condescending, basically a moron.Your methods are backward, are lacking inventiveness and ability and are tiresome. If you can't be bothered thinking outside the box then give it up altogether. Your methods are no different to anyone else's so you obviously can't think for yourself and just copy other peoples' work and claim it to be yours.

As proof of your ignorance, arrogance and rudeness, you still haven't even bothered to thank Steve for his kind comments in post #8. This in itself says plenty about your pathetically poor, pontificating attitude and personality.

[BIFanatic]

I just love how startled you are! He thanked me and you want me to thank him for thanking me ? what are you a kid still in his diapers/pre school. It must be really hell in that small brain of yours.

Editing my post again to annoy you.

Editing again, you are basically a dumb keyboard ninja, who has nothing else to do apart from being proud of his/her own foolishness.

Editing again because I made an error, oops, it must burn you to hell

[vcoolio]

He thanked me and you want me to thank him for thanking me ?

I just can't believe how thick you are!

You've just proved again that you are a complete and utter manner-less moron. You have no idea in your feeble brain (if you have one) that one needs to acknowledge the appreciation that a Poster shows. Have a look around any thread on any forum to see how any helper acknowledges a Poster's thanksbut, obviously, your over-inflated ego can't understand such a simple gesture. Its called common courtesy, etiquette. However, you've displayed your ignorance over and over again in just this one thread.

No, your editing doesn't bother me at all. It just proves what a feeble brain you havebecause you don't have the ability to create a train of thought. However, a Ninja can.

You're tiresome and boring with poor life ( and Excel) skills and a poor excuse for a human being (assuming that you could possibly be one).

Zzzzzzzzzzzz.....................

[BIFanatic]

Chill dude, now everybody knows that you are just a kid full of ego and a few line of VBA code that you stole from somewhere, I can tell that you are about to start abusing, because you are so annoyed.

[vcoolio]

........and a few line of VBA code that you stole from somewhere,

What's the matter? Still can't think of anything original?

Zzzzzzzzzzzzzzzzzz..........................