Copy rows from one worksheet into another based on single criteria

Hi Guys,


have a problem which I suspect will require a VBA solution and for the initiated I'm sure will be a simple thing to do but for an early learner such as myself is a right pain! I realize there are many posts online about vba code to copy rows based on criteria into another worksheet but they all seam too complicated for my needs. All i want to do is copy rows from one sheet to another based on a single criteria - not multiple criteria to multiple worksheet!


Here's the problem:

My workbook (see attached) has a sheet called "PANELS". Sometimes the rows in that sheet will have a "C" in column B. I would like to copy all the rows from the "PANELS" sheet that contain a C in this column into the new sheet "CNC SUMMARY" and paste that data from row 11 onwards in the "CNC SUMMARY" sheet.

So using the attached example I would like the rows in the "PANELS" sheet that currently shows Part no.s 'P2 - C' in row 12 and 'P4 - C' in row 14 to be copied and pasted into rows 11 and 12 respectively in sheet "CNC SUMMARY".


Greater functionality - keep sheet "CNC SUMMARY" updated if any changes happen in sheet "PANELS":

it is likely that sometimes after copying the appropriate rows into the "CNC SUMMARY" sheet that there will be changes made on the "PANELS" sheet. Admittedly I could write another macro to clear the "CNC SUMMARY" sheet before re-copying in new data but, even better, would be if the data on the "CNC SUMMARY" sheet auto updates as the info on the "PANELS" sheet changes.

So again using the attached example - if row 12 in the "PANELS" sheet no longer has a C in column B but then row 15 does then the result on the "CNC SUMMARY" sheet will be that Part no. 'P4 - C' will move from row 12 into row 11 on the "CNC SUMMARY" sheet and row 15 from the "PANELS" sheet will be copied into row 12 on the "CNC SUMMARY SHEET".

It would be great if this could all happen without my colleges - i.e. crotchety woodworkers using excel - doing anything although if that's not possible I could put a button on the "CNC SUMMARY" sheet saying 'Update' that would run the macro and I'd just have to remind them to press it regularly!



I hope that all makes sense. So to summaries: A macro to just do the copying/pasting bit if nothing else would be great although a macro to do this and also auto update (wether by a button or even a 'look no hands!' solution) would be totally amazing!!!

I'm really keen to understand this VBA malarkey so all help will be gratefully received.

Thanks in advance,


Oliver

You could do this without using macros, using three basic formulae. This would automatically update if you changed some values in the first sheet, so it would take care of your second concern. Are you interested in this approach?

Pete

Hi Pete,


Yes I might be interested in that approach although my one concern would be if that requires formulas to be put in cells that my colleges might then inadvertently delete and then complain that the thing don't work anymore!

If there is a way to put these formulas in un-delectable places then yes potentially could be a solution.

I would though want to avoid gaps between rows or un-removed rows if no longer relevant in the CNC SUMMARY sheet when updating but again if the formulas do that then great.


Thanks again

Hi Winon,

Are you able to elaborate?

With thanks.

Ah well, with Winon's encouragement here's the completed file.

I've inserted a new column B in the Panels sheet and put this formula in B10:

=IF(C11="C",MAX(B$10:B10)+1,"-")

This is then copied down to the bottom of your entries in column A, and both columns A and B can be hidden, so your sheet looks the same as before.

In the summary sheet I have this formula in A11:

=IFERROR(MATCH(ROWS($1:1),PANELS!B:B,0),"-")

then this one in C11:

=IF(OR($A11="-",$A11=""),"",IF(INDEX(PANELS!D:D,$A11)="","",INDEX(PANELS!D:D,$A11)))

and this one can be copied across row 11 to cell V11. I've used the Format Painter from row 12 to set the same format on row 11. Then the formulae in A11:V11 can be copied down as far as you think you may need them. Column A could then be hidden, as before.

This should give you what you wanted.

Hope this helps.

Pete

If you are concerned about your colleagues inadvertently changing the formulae, then once you have it set up you can apply protection to the summary sheet (with or without a password) which should prevent that happening casually.

Hope this helps.

Pete

Hi Pete,

That's insanely good and does the job perfectly!

As you say, I can then protect the summary sheet + I don't have to worry about my colleague not re-saving their copy of the file in the macro enabled format!


Thanks very much again. Look forward to building this spreadsheet up now!

Oliver

Glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Thanks Pete,

yes this has solved the problem and I will mark as solved as you suggest.

I have realized of a possible scenario that may scupper how well this works on a user experience front although it's not too problematic as I could 'encourage' user work-flow work-arounds but ... there could be occasions where cells on the panels sheet (most likely in the 'notes' column in column W) may be merged.

Is there a way - if say cells W12 and W13 on the panels sheet are merged and C12 and C13 both have a 'C' entered in them - that this merged appearance could be replicated on the summary sheet?

No worries if not - I can just tell my dear colleges not to merge 'C' rows and especially with non 'C' rows!

Thanks again.

Oliver

I think many contributors on here will agree with me when I say DON'T USE MERGED CELLS.

A formula cannot return a format - just a value, so there is no way that a formula can merge those cells in the destination worksheet if they are merged in the source sheet.

Pete

Ok.

That's fine as not a big issue.

Thanks again for solving the original problem.

Best.


Oliver