Copying specific data from multiple worksheets into one worksheet?

[Blarfengaar]

Hi,

I have a multi sheet workbook that I want to use as a project portfolio for our organization, example attached.

For reporting purposes I have created a Mgmt Rollup Projects sheet (sheet 1) and added a check box to Column D to all of the Project Team sheets. The intent is that if the check box is checked then a copy all data in that row should copy onto the Mgmt Rollup Projects sheet. The Mgmt Rollup Projects sheet should only include data copied from the team sheets with a check in Column D.

All sheets have the same formatting and column headings (starting on row 13), including the Mgmt Rollup Projects sheet.

I have tried VLOOK up and macros which pull data but isn't show up on the mgmt roll up page correctly.

I appreciate any help!

exec_project_portfolio_example.xlsx

[ARowbot]

Here you go...This works but with a few downsides!

1. Because you are copying so many rows it runs quite slowly (There is a way around this!)

2. Currently All checkboxes are also copied (Is this required?)
3. All your rows in Mgmt are a different size to the other sheets
4. The checkboxes in all sheets actually overlap onto the next row hence why I have had to use the top left cell in the code.

Please Login or Register  to view this content.

[Blarfengaar]

Thank you.

Responses to your feedback follows:

Not all rows with the check boxes need to be copied, just the rows with the check box actually checked. I inserted those using Form Controls, not ActiveX Controls. I didn't realize the column sizing needed to be the same. I can change that if it is necessary. I am running it now and you are right, it is quite slow.

[Blarfengaar]

How can I update the script to only include the check boxes that have been checked? I can't figure that out.

[ARowbot]

It does only include the check boxes that are checked already...Did you actually run it?
I didn't say column size..I said row size..I'm saying the data doesn't fit as nicely so that's easily changed.
It is only slow because there is so much copy and pasting. The code below will do the same job but without copying and pasting.

Please Login or Register  to view this content.

[Blarfengaar]

This is great! It works perfectly! Have a great day!

[Doc.AElstein]

This is great! It works perfectly! Have a great day!

Hi, Blarfengaar, ( and or ARowbot ),
. One quick question...


. I was going throught this code to
. a ) learn a bit about check boxes
and
. b ) the possibly offer another alternative ( possibly quicker solution )

... But I am a bit puzzeled: I find that both codes do copy the required rows, BUT also they copy lots of unwanted rows.
. Do you find that ?
. Thanks
Alan

[ARowbot]

I Suspect that it is because there are multiple checkboxes where there shouldn't be...For example on Team 4, There are multiple checkboxes in row 14. I assume this might be the case a few times..

[Doc.AElstein]

I Suspect that it is because there are multiple checkboxes where there shouldn't be...For example on Team 4, There are multiple checkboxes in row 14. I assume this might be the case a few times..

Hi ARowbot,
. Thanks for the reply, so its a case of the OPs sheet ? Some extra check boxes other than in column D ?
. Is it possible to constrain to looking in column D ?
. Sorry if its a dumb question - I am a Beginner and this is the first time I saw Check boxes. - This thread was a typical sorting thing I have been learning and the only new thing was the check boxes so i thought it would be a good Thread to answer and look at and learn something new... But you came in before I finished.. I thought then I would carry on anyway and use your check box bit with some of my ( Array ) code bits as an alternative, - but then I fell down trying to get your code bit to pick out the correct rows

Thank again for coming back to the Thread, appreciate it.

Alan

[ARowbot]

I was on about the extra checkboxes in Column D But you could put an If statement as follows:

Please Login or Register  to view this content.

[Doc.AElstein]

I was on about the extra checkboxes in Column D But you could put an If statement as follows:

Please Login or Register  to view this content.

... Thanks for that.
. It does not help the code to work.. And I am not "seeing" the extry boxes that are apparantly causing extra rows to be found. But maybe I am missing something -

. But thanks for the reply. I guess I need to read up a bit more.
.. basically i was hoping to get your code bit to give me the row numbers where the checked boxes are.. It does do that but then for all sheets gives extra values for lots of rows with nothing in and a coulple that have things in but either have no checked box .( that I can see ) . , or were already selected.
.. I am surprised the OP got the code to work. Maybe he is happy to delete manually those extra produced rows..

Thanks again anyway for the reply

Alan

[ARowbot]

If you use the following code..It will highlight to you where some of the rows have multiple checkboxes (On top of each other). It will only show some because the one it highlights may look correct even if there is another one underneath it. blarfengaar...You need to check your sheet to check that a row does not have multiple checkboxes in or it will cause problems.

Please Login or Register  to view this content.

[Doc.AElstein]

If you use the following code..It will highlight to you where some of the rows have multiple checkboxes (On top of each other). It will only show some because the one it highlights may look correct even if there is another one underneath it. blarfengaar...You need to check your sheet to check that a row does not have multiple checkboxes in or it will cause problems.

......

.. Thanks for coming back with that.
. Basically I did something very simlimar . I commented out the action bit ( Copy paste, your first code , or the .Formila = bit in your second code. )
. Then while stepping throgth wiith F8 I placed the cursor on r and / or nr and saw it was counting too many, explainign why the original full codes were bringing in too many rows

....some of the rows have multiple checkboxes (On top of each other). It will only show some because the one it highlights may look correct even if there is another one underneath it.....
....

. that's the bit that has cleared the mystery for me.
...



.. I am new to check boxes / shapes etc...
. It would appear it is only possible to go througt the checkboxes in the order that they are put in..???
. - So a code like yours will rely on the check boxes having been put in sequentially in the rows in a single column ( AND ONLY once!! ) ( And there must be no other "Shapes" or they will also be accesed, leading to extra r or nr values .... )
.

. I am still reading up, but do not see yet how to get at alist to show where the check boxes are, so that in such a case I could check for, and delete , any extra multiple check boxes in a cell ( or for that matter any other check boxes in the sheet any where else which would also chuck a spanner in such a code.
.
. Thanks very much for coming back again here.

. Shame the OPs feedback was not more detailed...

Thanks A robot
Alan