Help reduce code

[carlito2002wgn]

I inherited a template that I use frequently. It has several macros, but there is one that I would like to simplify/reduce if possible.

My best understanding of what it does is, it selects and copies a cell on one sheet (POC Calc), and then uses that selection to choose an option from a dropdown list on another sheet (PM input). The option from the dropdown list populates another range of cells which then gets selected and copied. The data that was copied is then pasted into a cell back in the POC Calc sheet.

It then goes back and does the same thing over and over again, until the list on the PM input sheet is exhausted. I currently have to manually adjust the number of times the code repeats based on how many items are in the list in the PM input sheet or the number of populated cells in the range on the POC Calc sheet (The list in the dropdown of the PM input sheet and the items in the range on the POC Calc are identical).

Can the code be simplified by using some type of loop? This would be great so that I don't have to manually update the code each time I use the template.

(Sample file also attached).

The person who built the template placed a button at the top of the POC Calc sheet to run the macro in question.

Thank you!

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[carlito2002wgn]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

Thank you, Trevor. I hesitated to upload the file originally since I wasn't sure how to "shrink" the file and still have it do what it is supposed to do. The file is now attached to my original post (gutted as much as I could).

[TMS]

There's something very strange about the file you have uploaded. I have tried twice to open it and both times it has crashed Excel AND my machine.

I have opened recently downloaded .xlsx and .xlsm files without issue so it is very definitely something to do with the file. It is unlikely to be the VBA as that is not enabled (by default). What did you do to the file when you "gutted it"?

I'm not trying for third time lucky.

[carlito2002wgn]

There's something very strange about the file you have uploaded. I have tried twice to open it and both times it has crashed Excel AND my machine.

I have opened recently downloaded .xlsx and .xlsm files without issue so it is very definitely something to do with the file. It is unlikely to be the VBA as that is not enabled (by default). What did you do to the file when you "gutted it"?

I'm not trying for third time lucky.

I am sorry about that, Trevor! I am just returning from Thanksgiving holiday here in the US, but will look into the issue. As far as how I "gutted it", I simply deleted some sheets (I do not remember doing anything else). I will send it to my wife to see what happens Thanks for trying!

[TMS]

No problem.

[Fluff13]

Opens quite happily for me.
How about

Please Login or Register  to view this content.

[carlito2002wgn]

Opens quite happily for me.
How about

Please Login or Register  to view this content.

This is awesome! It works perfectly (and FAST).

I feel like an ungrateful child asking, but is there any way to alter the code to account for the fact that the list of resources will not always be 3 To 39? It will always be from 3, but might be shorter or longer depending on the project.

As mentioned in my first post, the list of resources is manually pasted by me each time I use the template (I won't be so greedy as to ask for help automating that!). If it is difficult to alter the code to count the number of resources each time I use the template, would it be easier to just increase the length of that range be default, say, 3:60 instead of 3:39? Obviously, I would need to expand the range in the template as well as the code.

[Fluff13]

How about

Please Login or Register  to view this content.

This will look for the word Total in col A & run up to the row before.

[carlito2002wgn]

How about

Please Login or Register  to view this content.

This will look for the word Total in col A & run up to the row before.

Bravo!

This is perfect. Thank you so much!

[Fluff13]

Glad to help & thanks for the feedback.

[TMS]

Glad you have a solution. Opens ok on my iPad but, for some reason, problems on my laptop. Who knows … ?