Pasting entire row to a new sheet if cell contains certain text

[bakerbaz04]

Hi there,

I am looking at trying to develop a workbook for my job that would make things alot more efficient. This involves a few things that i am hoping to get some help with.

Firstly, I download data from a web based app into excel. I now want to take certain full rows and paste them into another sheet depending if they have certain text in a cell. So i want to search the entire coloumn for a value of "OR" and if this is present copy the entire row into a new sheet ?

Does this make sense and is it possible?

Thanks

Mark

[HaHoBe]

Hi, Mark,

welcome to ExcelForum. Please have a look at Forum Rule #1 and change the thread title according to this rule.

Ciao,
Holger

[Pepe Le Mokko]

Hi
to make a better chance of getting an answer, please adapt your thread title per forum rules - Thx

[bakerbaz04]

Apologies, Hope this is sufficient

[HaHoBe]

Hi, Mark,

if the range shows headers in the first row you could use the Advanced Filter with both Criteria range as a well as Output on a different sheet starting the Filter from that sheet. This would copy over all rows which meet the criteria.

If you want to apply VBA you should consider to use the Autrofilter and narrow down the number of row to just those that fit, check if any records are listed and copy over the visible range to the destination sheet.

The last and most time consuming way would be to run a loop over all the records and compare each cell froma column in every row to match the criteria. If so, copy over the row to the destination sheet.

You may record the action you take with the macro recorder (make sure that the developer tab is made visible via Options) and take that code as a start which would need some work as the macro recorder usually hard-codes all ranges and needs to Select before taking any action to cells/ranges (which you will not if youīre writing the code on the scratch).

Ciao,
Holger

[bakerbaz04]

Without trying to sound ignorant (its been 10 years since i used VBA/macros) is the range just defined as the cells you wanted to check? so if i wanted to check the entirety of Column F, the range would be F:F?

[HaHoBe]

Hi, bakerbaz04,

maybe use

Please Login or Register  to view this content.

to check the cells below the header to the very last filled cell in Column F.

Ciao,
Holger

[bakerbaz04]

Holger,

What code do I put in to search the range for the text "OY", then paste this entire row into new sheet if "OY" is present ?

[HaHoBe]

Hi, Mark,

you would need to narrow down the question if the item is the whole content of the cells or a part of it.

Please Login or Register  to view this content.

If you want to search for a part of the cell you could use

Please Login or Register  to view this content.

or use InStr to find out the first position of a possible match in the characters of the cell.

Ciao,
HOlger

[bakerbaz04]

So basically this is what i want to acheive ...


Consist Assest Defect Code

BN01 12456 UI
BN08 12457 OY
BN24 11232 EJ


I would want the whole 2nd row to be pasted into a new sheet. then any other rows with OY in the "Defect Code" coloumn to also be pasted into a new sheet, i will then be wanting different defect codes to be pasted to different sheets?

Regards

Mark

[HaHoBe]

Hi, Mark,

depending on how many items you want to find and the names of the sheets you could use either a Select Case if the item is the whole contents you could apply the destination name to a variable and use that for copying.

I would suggest to use two arrays (one holding the items to find, one for the sheets to copy to), use the Autofilter and copy over the visible area and either add beneath whatīs already on the sheets or delete the data on eahc shirt and then add..

Ciao,
Holger

[bakerbaz04]

Holger,

using to arrays sounds good, where do i start? apprecitate your help and apoogies for being a pain.

[HaHoBe]

Hi, Mark,

could be solved like this:

Please Login or Register  to view this content.

Ciao,
Holger

[bakerbaz04]

Its giving me an erro message around the Set wsData=Sheets(cstrDATA)

I should mention that i have the raw data in sheet 1 and want to paste it into sheet 2, onve i have this sussed i can assign it to a button and make the interface more friendly

Cheers

[bakerbaz04]

Its giving me an erro message around the Set wsData=Sheets(cstrDATA)

I should mention that i have the raw data in sheet 1 and want to paste it into sheet 2, onve i have this sussed i can assign it to a button and make the interface more friendly

Cheers

[bakerbaz04]

error 9;

Subscript out of range

[bakerbaz04]

error 9;

Subscript out of range

[HaHoBe]

Hi, Mark,

you would need to change the name of the constant to suit your needs for the sheet which holds the data - I havenīt seen anything from your workbook so Iīm off to guess, and I may assure you that I ran the code in a sample workbook in Excel2013 and the code didnīt raise an error before posting the code here.

Ciao,
Holger

[bakerbaz04]

Can i upload a sample for you ? how do i do that

[HaHoBe]

Hi, Mark,

Remember to desensitize the data in the workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Ciao,
Holger

[bakerbaz04]

Prototype1.xlsx

Hopefully this has worked, so basically i want to copy and paste anything with an O in column F into the "Doors" sheet, if a "UW" is present in column F paste entire row into "Wheels" sheet, and if "CHECK" is present in column E paste entire row into "Checks" sheet.

then i want to assign this to a command button.

Hope this makes sense

[HaHoBe]

Hi, Mark,

what should happen with rows 41 ansd 42 as they meet both the criteria in Column F as well in Column E, should these lines be copied to both sheets? And there is a problem with all the rows you marked yellow as I donīt see any reason for rows 16 or 88-95 to be marked.

Using some simple additions like headers in the first row of data would enable you to use a better method than looping through all the data but I mentioned that way before in this post. So again: this canīt be the fastest of all codes as you by now explain that you will need to check to two Columns independly on each other and start your data on the first row.

Please Login or Register  to view this content.

Ciao,
Holger

[bakerbaz04]

Holger that is fantastic,

apologies on the "Check" sheet there was not supposed to be any raw data in that sheet but i forgot to delete this. If the both are contained in one row then yes i would prefer if the row could be pasted to both sheets if this is possible? I am going to edit headers now for the columns to make this easier

[bakerbaz04]

Also im not sure if this is possible but can i run a seperate macro attached to a command button to clear these sheets? as the raw data will change depending on when i download it? Would of loved to of created a macro for the download side of this process but seems this cant be done

[HaHoBe]

Hi, Mark,

Please Login or Register  to view this content.

Yes to the next 2 questions in general but lacking of information you should consider to start a new thread and be more precise than

Would of loved to of created a macro for the download side of this process but seems this cant be done

The Magic Ball is broken, and thereīs a lot of snow but very little exact information on what to download from where, how to do so, where to store, how and if to smoothen the data if needed.

Ciao,
Holger

[bakerbaz04]

Thanks Holger

Do I input that code as a seperate macro or incorporate into the existing "run me" button?

I will start a new thread shortly for the other issues. You guys really are incredible.

Many thanks

[HaHoBe]

Hi, bakerbaz04,

sorry I didnīt find any button in the workbook - if all three macros were part of the button I would comment out the original text and assign the last posted procedure to that button,. If any other modification is made in that button I would use a different button and name it accordingly (or think about implementing an own Tab into the ribbon to hold the macros related to the workbook which would need some XML-Code to do so and an external UI Editor).

Ciao,
Holger

[bakerbaz04]

Holger,

Sorry I confused that,

What I meant was; if I wanted to put anything with check and UW in both sheets, do I need to input the code into the macro you assigned to the "run me" button.

As it turns out I would just like the ones that meet both criteria into the checks sheet, so if a row has UW and Checks it would only paste to the Checks sheet. Is there anything I need to Alter to acheive that ?

[HaHoBe]

Hi, Mark,

if I read the code correctly I would say that the code from http://www.excelforum.com/excel-prog...ml#post3751763 should do what you want.

Ciao,
Holger

[bakerbaz04]

Holger,

This code works fantatsically well, appreciate all your help, one last question, if i wanted to add other sheets to paste certain data, i.e. if i wanted code P1 to go into a new sheet what code would i need to enter in the current code and where?

Thanks

Mark

[HaHoBe]

Hi, Mark,

Please Login or Register  to view this content.

needs to be changed to

Please Login or Register  to view this content.

where new sheet should be changed to the proper name of the sheet.

Ciao,
Holger

[bakerbaz04]

I have altered that but for some reason now it is not picking up "O" and moving them to doors. any ideas

[HaHoBe]

Hi, Mark,

and what shall I see in a workbook without data and without code?

Ciao,
Holger