I've been tasked with cleaning up my company's report. I like to pretend that I'm competent in Excel until I attempt to create a macro. They're the bane of my existence but, with what I need to do, I have to create one.

I've attached a simplified version of my report but here's what I need to be able to do:
Upon the update on a cell (c1) I need to have cells C3-F6 sorted descending.

It always seems simple enough but I've been googling all over but I cannot figure out how to make other people's macros work for me. Any help with this matter would help tremendously. Keep in mind I'm VERY new to macros.
This may be a stupid question but do I need a macro or can this be written in VBA so that it recognizes that cell c1 has changed? Apologies in advance for the stupid questions!

In the meantime I'll be cursng Microsoft's name and attempting to fry my computer's motherboard with my tears.



Thank you!

Attached Files

MTD Summary Example.xlsm (13.0 KB, 8 views)

Peter, I looked at this workbook but I'm confused; I thought I'd find a worksheet with data in it and also a first attempt (if only that) at a VBA program. But there's hardly any data in the worksheet and no VBA code at all in either of the two modules. So I guess (now that I reread your question) that I was mistaken; I was sort of carelessly assuming that you'd made a start on the program but don't know how to do the sort part, but now it sounds like you're not sure how even to start.

Yes, you can tell it to do the sort automatically when C3 changes. It's slightly advanced—most people write simple macros first and then take up automatic ones only later—but the difference is not in the VBA code itself, but rather in where you put the code and what you name the subroutine; Excel is very particular about both, unlike what happens in most programs, where you can just name them any old thing that occurs to you.

To make a program run automatically when someone updates a cell in a worksheet, you have to 1) place the program code in the module that pertains to that worksheet, and 2) define the program very exactly.

1) It looks like you've already created two program modules in the sample workbook you posted. But to get Excel to run this program whenever someone updates a cell in Sheet1, you have to place the code in Sheet1's module. Not in the worksheet itself, but in the corresponding module in VBA; that is, you go to the VBA Editor (as you did before), then look for your workbook in the "Project window" (probably at the top left) and double-click on the object labeled "Sheet1". That's where you'll place this program code. If we need to trade screen shots to make this clear, let me know.

2) The name of the program can't be just something you make up. Programs that run automatically when triggered by an event in some object have to be named for the event and the object. In this case the event is named "Change" and the object is "Worksheet"; the subroutine name is Worksheet_Change. But wait, there's more: It must be a "Private" sub, and it must have a single parameter, which you can name anything you like but must be defined as "ByVal <variablename> As Range". There's a sample Change routine at this URL that may help, though of course it doesn't do a sort for you too; it just tells you how the thing has to be defined.

Me, I suggest you start with something easier. Make a subroutine like this:Then go to that worksheet and change something—or just hit the <Del> key on an already-empty cell—and watch the program work.

That'll give you a feel for it. You'll notice right away that this thing runs when any cell is changed. But you want to do it only when C3 is changed, right? Well, coFrom is what I named the parameter in the sample above; that comes to the program pointing at the cell that was changed. So you can have your program check to be sure the cell is C3, and if it's not, just exit the program. Then go ahead and do what you want.

But one step at a time. If you're as VBA-illiterate as you claim—if you're not just being humble—let's start with just this and add more parts after you see how this much of it works.