Excel VBA to Copy and Paste Rows if multiple Conditions is Met

hi this is amazing Kent in this video I'm gonna show you how to do these tedious tasks such as this we want to copy these one wherever the category is a want to copy it and paste it here but it's two again right it's copy this and paste it here and over here copy again and paste it here right you do this until the end of it so how do you do that so we first will create a command button here so let's show you how to do that so we go to the Developer tab and go to insert and click on this command button again right come on button click here and drag so you have this data and we'll change this by right click on the command buttons and choose properties and and the captions you can just change anything you like and then right click here on the buttons and choose view could I would have these informations and what we do is that first we want to know the last row of this data here so we're gonna use last row and equal and the worksheet name is called data so we have worksheets then we want something from the you can come something from the end right by using a and roll stock town in SL up rose so that would be the last row so how do we know whether that is the one right you can use the debug print classroom so right here we go to will choose the Amir windows it's some way so that's one so just run this could yes 22 what does that mean 22 22 means here the last row here okay so we got it let's come back here right and want to copy this data here so how do we copy the data right it's just we just remove this and want something to do a loop by checking each row so we have the follow and start with the purpose our and start from Row two to Row 2 to the last row so what's the last row Las Ramblas right here so we can just put that copy this paste here next so this is to check row by row and for each row we want to check on the column tree it's the country so how do we specify that's column right we can copy days and here the country see and the the row number for the cargo is r so using that we can use this if dot value equal to a then we do something right so what do we do so is this a we will just copy the role so to copy the role we make yourself taste similar to taste right and what we do is that dark rose ah don't copy yes right we choose to copy it right so let's just do until this one we'll put it back here and we run the code and we so as you can see that the first row is now copy but it doesn't pays it to somebody else right so how to pee right so we got to say activates the repulsive field reportable so we call it this thing and door to activate okay and wanna pee to this row right which is the next available Rose right so how do you do that we use a very similar process right this year we just want to know the last rule in the report and this is just report and for these write about the same and so we have this and finally we got to copy right copy to that reports judge the ring and basic cottage thing here and then finally we pays it is to pay zero we have the active sheets dot pace all right let's take a look let's see if it works so one is good all right all right all right so it does not work somehow it does not work let's see you run D so it does work at each sheet up pace hmm yeah I need that that's like great does it weird that way will sheets report hmm this one is two reports so reports and with this range air one and pasting all right it's words except that you replace the first rows or what we do is that plus one to get right so copy days basic here right that's the manner step and then run it face it all right you can see words right so every VOC just let it runs right yes that's it so it's copy all this into the reports so max is that what if we want to copy itself copy everything here we want to copy only the a.b a.b copy the a B and the E column right how do you do that well we can change this right so this is no longer needed I'll just go we can just copy it copy and paste it somewhere so we already have done this this data right here so basically we have this right um we have this data here it's only well actually we can just keep it there all we need is just changing this one single row which is this role so itself copy the entire row right so we decide to remove it but it is commenting it and now what we do is that you want it just a be and year right so copy a B and E so it put a B and yeah change you to e so just delete this row and and we run this code now I can see that it is really just a B and basic here this is not needed okay these are all for the a category titles or and put into here yeah that's it right so that is some our modifications with copy only a selected column so you can change that and and then the last one we want to explore is that we copy with multiple conditions right to have multiple conditions right instead here in the comparison here right well what we want to check right we look at this column C now this column C right this is a and we want to something that is region is the act so what we do is that the same m put a n functions we use an underscore then you to the next line and right here which is two acts and then we can run it right itself T's right we have more conditions to check so we should have lesser row step so we're done right there's none let's see oh yeah that's because we got like to look at C this is D column D so as C will look at a in column D of the same row we look at X right so this is the amount of conditions right so you have two n functions right good alright so that's it these are the list of them so let's do a recap right um we we stop forum doing checking if that row of the categories a that we copy these and put it right here and that could is show here right and the second modification we do is that we copy only select the columns right so and you gotta change oh yeah that's you change that to this line right you specify which row if it's continuously you can put from the stopping column and the any column and the individual column is separated with a comma and the third one is that what if we look at multi Birkin dishes we use the N function and that's it this is how you use a follow and it functions and multiple n functions to copy and paste that illustrate how to use the follow and if thank you very much right so if you think amazing and believe you're amazing you can do amazing things thank you

Channel: Kent Lau Chee Yong

Views: 62,974

Keywords: excel dashboard, excel, corporate training, malaysia

Id: mv77WjuELpo

Length: 12min 41sec (761 seconds)

Published: Tue Jun 18 2019