Best direction based on need - VBA or Macro or?

[tlock0331]

Here is the scenerio:

We have a drop down on a Cell (Data Validation), based on that selection, i want to copy a range of cells (always C though S number of rows will vary from 4-8) from another tab. The bottom row of cells will contain another drop down for the project manager to select another component and start over again.

I am trying to learn how to make macros and VBA, but no where near good enough, so i am interested in peoples opinions on a good direction to take with this.

I wont lie, i also bought a couple of VBA and Macro for dummies books yesterday, doing things like this are new to me, so my experience level is at about a 1 with that, formulas and whatnot im okay with.

Thanks for taking the time to read this.

[Pauleyb]

I started with a book. Excel 20xx Power Programming with VBA by Mr. Spreadsheet. I am a programmer, so quite a bit came natural, and I just needed to understand the excel parts of it. Probably any book will help you with your current issue.
Basically, reading a cell's value and then copying a range. The trick here is if you want to react to a change event. Again, most books will have that.

With that being said, quite a bit is now online from when I started. You could probably find your answer online, but the book will help you refine your query and better understand the solution.

[tlock0331]

PauleyB,

Thank you for the reply, I will have to go look for that book.

Throwing this out there as a thought and i probably should post my spreadsheet on this as well to help make more sense. I have an "Instrumentation List" tab, and on the initial E9 cell is a drop down box to select which catalog item is needed, that information would be pulled from "Component Category" tab and pasted in with other drop downs for project managers to fill out.

I thought about making a subroutine for each component and then from the "Instrumentation List" drop down, have the cell below that run the subroutine that matches the text in the drop down.

I am still experimenting with even how to start a VBA project etc etc. so all of this is a learning curve on a large project.

[Mumps1]

I found that the best way to learn VBA on your own is to use on-line tutorials, searching the web for specific areas of interest and following forums such as this one. The volunteers on sites such as this one are invaluable at providing help. I would suggest you keep a file of codes that you find useful and in this way you can build up a library that you can refer to. A lot is also trail and error. The very first post in this Forum is a good place to start. These links are also valuable:

https://www.mrexcel.com/forum/lounge...ml#post5235342
http://www.snb-vba.eu/index_en.html

Regarding your problem, please post a copy of your file and explain in detail what you want to do using a few examples from your data and referring to specific rows, columns and sheets.

[tlock0331]

Mumps1,

Thanks for the links. Ill start checking them out, i am trying to test out ideas, and refer my books and online help.

However, attached is the spreadsheet i am working with.

The plan is this:

On Tab "Instrumentation list" The PM will start with cell E8, as a drop down, and select the item. That will grab the correct item from the list on the "Component Category" tab.
so if "Blower - Pressure" was selected, then Cell B2:U14 would copied and pasted in on the Instrumentation List.

I know this should be simple, but the list of equipment is going to grow a lot and since each instance of equipment will vary in row height, i thought it would be better to bring in
the cell under the "----Select-----" so that once the Select was changed, it would trigger to go grab from the component list.

Thats one of the reasons i have bounced the idea of creating a subroutine of every item in the Component Category, instead of writing a 50 statement "IF" line.

Thanks again, for any help you can provide.

[Mumps1]

I'm sorry but I don't follow. If you select "Blower - Pressure" in E8, you want to copy B2:U14 in the Component Category and paste it to B2 of the Instrumentation List? Could you post a modified file with 4 or 5 component categories and what the end result would look like for all 4 or 5 categories in Instrumentation List? Please be very detailed referring to specific cells, rows, columns and sheets.

[tlock0331]

Yea, i screwed that up....

If you select "Blower - Pressure" in E8, I want to copy B9:U14 in the Component Category and paste it to B9:U14 of the Instrumentation List.

Once that has been copied and pasted over, then in this instance, "E13" will contain the next drop down, so if "Blower - Vacuum" was selected, then B14:U21 would be copied and pasted over to
B14:U21. Once each equipment item is complete in the "Component List" its going to stay stationary. So far i have thought that having the drop down box selection in each component category would make it easier
for coding proposes.

I also slightly changed the look, in what i think would make it easier. again, nothing written in stone as far as a lay out. I only have the two components done at this point,
"Blower - Pressure" & "Blower - Vacuum" but with the newest attachment i think it makes more sense

The components are a work in progress, before the next addition we have to approve it, so this is all i can get done for now. I kinda kept thinking that if i could get a general layout scripted i could probably
rinse and repeat.

[Mumps1]

Try the attached file. I have made some changes to the "Instrumentation List" sheet. You will see a drop down list in cell A7. Simply make a selection in that drop down list. I have also deleted the contents of column A in the "Component Category" sheet.

[tlock0331]

WOW! i really dont know what to say, that is impressive! I have been reading through your code and i cant thank you enough for taking the time to do what you have done for me.
I would have never gotten to where you did and with what i think is very little code. Ive spent at least a week playing around with different ideas and trying to learn more about the VBA process and you smoked me,
I expected mine would be a mile long. lol

I am really am enjoying looking at the code you have written and trying to reverse engineer what you have done and actually learn it. there are some other things i would like to do, so i am hoping i can learn from this
and incorporate some added features in the future.

Again, i cant thank you enough for everything!

[Mumps1]

You are very welcome. I forgot to mention that the code relies on the current shade of green you are using in your "Component Category" sheet so if you make any changes to that sheet, you have to make sure you use the same color in column E.

[tlock0331]

i saw the color part, i was thinking that was setting the color, not measuring against it, but thats fine, its not going to change, well, at least i will change it and see if i can keep everything running.
one of the things i like about what you did was allow for dynamic changes on the "Component Category" tab. i have been messing around with things and from a functional standpoint it great!

[Mumps1]

If you change the color (this applies only to column E), you will have to change the color number in the code to match.