Solved: [auto]Sort Different Worksheet

[thomaspatton]

version : MS Office Excel 2003 (11.8105.8107) SP2

First, I'm a complete newb to VBA. I've never done work with it and honestly don't currently have the time to learn the little nuances. It's taken me long enough to learn about Excel formulas using the Help menu. Anyway, whining aside, here's my problem.

I attached a sample of what I'm currently working for my Unit. It has 1 Master Roster [worksheet] that has 3 Reports [worksheets] linked to it. This is done through the cell formulas. Each Report sheet needs to be sorted by column A, B, or C corresponding to the Master Roster. I left my "Record a Macro" macros in there to maybe clarify this. The macros work perfect for what I need WHEN I'm viewing the Master Roster. However, what I'm trying to figure out is either :

A) Place a macro button on the individual Report sheets to sort the Master Roster according to the requirements for that Report.

B) Have the report Sheets automatically sort when viewed, based on the sorting requirements for that Report.

Like I've said, the closest I've gotten is the "Record" function that does it when I add a button on the Master Roster sheet. But when I add a button with the same macro to the Report sheets, it tries to sort that Report sheet. I went through the help files and tried editting my macros with Worksheet("Master Roster").Range("A3:T100"), but all that accomplished was to give me my queue to finally ask for help, lol.

If someone can help me out with a macro for the first report, I'm pretty confident I can change the references for the rest of the reports. I just can't figure out how to edit my macro's appropriately or make it autosort on open, if that's even possible with something like this.

Any help, even a finger in the right direction, would be immensely appreciated.

SGT Patton

[lucas]

There are better ways to do this but the first problem I see...
for example in your Sort_A you have this line:
Range("A3:T100").Select
which includes the header info and merged cells...thus the error.
Try changing it to something like this for the A report code:

Range("A12:T100").Select

[thomaspatton]

Dangit. I was thinking about my post on the way home and was sure that I didn't clarify. Knew this was gonna come up, lol.

The Header columns with merged cells on the Report sheets are actually going to be printer page headings. I deleted the rest to aleviate confusion. You can see what I mean if you view Page break.

Actually, what I'm attempting to do is ONLY sort the Master Roster. By using the formulas on the Report pages, the information will show up correctly if only the Master Roster is sorted. So, if I click to view the "A Report" sheet, for example, it will sort the Master List by C:Descending and then by D:Ascending. In other words, everyone in "A" group will be filtered to the top on ONLY the Master Roster, resulting in an accurate view of personnel on the "A Report". I don't want the Report sheets sorted at all.

My problem was that I recorded my macros while viewing the Master Roster sheet, but then when I tried to use it while viewing the Reports, it came up with an error. I honestly don't even know of it's possible to designate a set sheet within a macro like this. I've played around and haven't figured out how, anyway.

[SamT]

Try this attachment.

My Excel warns that your workbook is a different version,
and;
VBA errors out on the SortCriteria code during Debug.

SamT

[SamT]

Ooooops!

Attachments

[thomaspatton]

I'm not understanding. I opened it but the command buttons you added don't do anything. Also, the macroes do the same thing.

Is this just not possible?:dunno

[SamT]

Tom,

I'm a real newbie to VBA myself, but that is one way to use a button to call a macro. Your version may use another. Look in Help for 'Assign,' 'Macro,' and, 'button.'

Because of the differences in versions, I wouldn't be able to T/S your sortcriteria.

If you don't specify which sheet to sort, it will try to sort the Active sheet.

[thomaspatton]

I had the button thing down. You can actually attach macroes to any embedded object,like WordArt and such, that I've tried so far. It's the actual code of the macro that's lost me.

Basically, how can I open up "A Report" worksheet and have it sort only the "Master Roster". Or, be viewing "A Report" and have a button set to activate the macro that sorts only "Master Roster".

I hope I'm being clear enough. If not, let me know what I'm missing. Once again, any help is definately apreciated. Thanks to everyone who's replied so far.

[SamT]

Tom,

Damme, I wish I could be more help. I'm really just a wannabee at the moment. Life has forced me into another career, again, This time Excel and VBA. . . and . . . .

And I really really want to be able to code all these ideas in my head. I spend about 4 hours a day here just reading and thinking.

Concepts I've always been good at, implementing this one of understanding VBA is rather spikey at the moment.

But, in answer to your question, yes this is possible. Somehow.

SamT

[thomaspatton]

Same thing here, Sam.

Closest thing I can think to do from reading is change my original line :


Range("A3:T100").Select

to something like this:


Worksheet("Master Roster").Range("A3:T100").Select


But, when I try that, it just errors out completely. I gues I just haven't grasped how to define tags yet, lol. From what I've been reading, My understanding is that if I define the specific Wroksheet as above, it should only sort that Worksheet, regardless of which sheet your viewing in the same wrokbook. Alas, if it worked, I wouldn't still be posting here, lmao.

Wish ya luck in your endeavors. Hopefully, someone comes along if this post stays "Unsolved" for too long. Lemme know if you find a work around in your dabbling.

[lucas]

Thomas,
I am still having trouble understanding what your objective is...
You wish to view a report sheet but from that sheet you wish to sort the master roster...? is that part correct?

[lucas]

I will say this...you are using a lot of merged cells which will make filtering or sorting very difficult. You should get in the habit of using "center across selection if you are going to try to filter or sort your data. Also it would be better if the names were on the extreme left of the sheets.

[lucas]

I think this can be done but you will have to re-organize your sheets according to my previous post. Then you can use something like this to copy the values(not the formula's) from sheet H to the master:
Range("B10:C29").Select
Selection.Copy
Range("C31").Select
Sheets("Master Roster").Select
Range("Y5").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Columns("Y:Y").ColumnWidth = 15.86
then you can use that info to filter the master...
something along the lines of:

Range("a3:e30").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range( _
"Y5:z15")

but the merged cells and the order of the data is a problem as it is....

[thomaspatton]

k, I'll explain everything more simply. Having merged cells should not play a part in sorting anything, since the Sort Range doesn't involve any merged cells. Regardless, here is what I optimally want to happen :


1> I fill in the Student information on the "Master Roster". The "Master Roster" will be the only worksheet that will be typed on, besides "Data" which doesn't play a part in my sequence. For students, Range A3:T100 will be filled out completely and be student specific. Most important columns for my macro are A, B, & C. These 3 columns will initially determine how the "Master Roster" get's sorted.

2> The data in Columns A, B, & C of "Master Roster" correspond to Worksheets "H Report", "E Report", & "A Report" as noted by the header column A2, B2, & C2 in "Master Roster".

3> After I am done filling in Student information, in no particualr order, I click on Worksheet "H Report". It automatically sorts my information. The sorting is done ,first by ColumnA:Ascending and then by ColumnD:Descending, BUT will sort ONLY the "Master Roster" RangeA3:T100. It will not sort anything on the active "Report" sheet.

4> What this accomplishes :
? I only have to worry about entering data on "Master Roster".
? When I view the "Report" sheets, the different reports will all sort "Master Roster" differently based on Columns A, B, & C. For example, sorting for "E Report" would sort ONLY the "Master Roster" rangeA3:T100 and put any "YES"'s in ColumnB3:B100 in order with "YES"'s first, then sort all the "YES"'s alphabetically by ColumnD3:D100.
? All the different "Report" sheets can have different sorting criteria based on the appropriate column in "Master Roster" without having to worry about editting anything. Any data that has a "NO" in rangeA3:C100 will not transfer to the "Report" sheets.

Appreciate the thought Lucas, but I don't wanna use a "copy/paste" macro. The way the sheets are managed internally with the Cell Reference formulas makes it perfect for what I need.

If I manually use the Sort command on the "Master Roster", I get the correct information displayed on the "Report" sheets automatically. However, I'm trying to figure out a way to automate the sorting process so it sorts the "Master Roster" by the apropriate information that the "Report" sheet needs.

Hope this clears things up a bit.

[lucas]

If I manually use the Sort command on the "Master Roster", I get the correct information displayed on the "Report" sheets automatically. However, I'm trying to figure out a way to automate the sorting process so it sorts the "Master Roster" by the apropriate information that the "Report" sheet needs.


Please describe in detail the process, which choices, etc. you make during this process. Step by step please so I can understand what your trying to do.

[thomaspatton]

I went and read some posts and found that macro code for automatic "happenings" needs to be added to the actual sheet by right-clicking the Sheet name Tab and selecting "View Code".

I put this in there on "E Report" :


Private Sub Worksheet_Activate()
Worksheets("Master Roster").Range("A3:T100").Select
Selection.Sort Key1:=Worksheets("Master Roster").Range("B3"), Order1:=xlDescending, _
Key2:=Worksheets("Master Roster").Range("D3") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub



However, after I deselect and re-select the sheet, I get a "Run-time error '1004': Select method of Range class failed"

Maybe this can help get in the right direction. I know i'm losing time on it, lol.

Also, to be more specific about my sorting criteria. to get "Master Roster" sorted so that "H Report" has accurate info in it, I do this :
http://vbaexpress.com/forum/attachment.php?attachmentid=4139&stc=1&d=1161805016

Then, "Master Roster is sorted appropriately and "H Report" shows up correct. If I wanted an accurate "A Report" I would change the first Sort Criteria to Column C.

This is what I want t happen automatically everytime I open the various "Report" sheets.

[lucas]

thats ok SGT,
I just haven't gotten my head wrapped around your objective yet....we'll get there even if we have to recruit help...

[thomaspatton]

PROGRESS!!! And it's POSITIVE Progress!!!!
I read on some more and made some code alterations after a new "Record Macro" cheat-session.

Now, automatically when i select the "H Report" sheet, it goes back to "Master Report", selects A3:T100, then sorts it according to H Data in ColumnA. Then it goes back to "H Report" automatically! All of this code was put into the "Right-Click-Sheet-Name_tab-and-View-Code" VBA section.

But, as you might have already guessed, since it auto-runs the "jump-back-and-sort-and-jump-back-again" macro, it creates a nice little constant loop of jumping, lmao. Was kinda trippy at first, however, not entirely the effect I wanted.

I reattached the book with the new code. Select the "H Report", watch the whirling mess, then hit "ESC" so you can step into debugger. Maybe you can figure out a way to stop it from looping. Maybe some way to make the code only execute whenyou MANUALLY select the sheet, but not when it's AUTOMATICALLY selected. This might not be possible at al, but it's a step in the right direction in my book.

[lucas]

If you remove this line it will stop the looping...it is reactivating sheet H which triggers the sheet activation code..

Sheets("H Report").Select

[lucas]

The problem I see with this is that you won't be able to select the H sheet because it will trigger the macro...you might just want to add a button to the H sheet to run the macro.....just a thought.

[thomaspatton]

That's what I plan on doing as a first step to solve my issue. I've already added it to my Actual Project.

However, and no "insult" intended, I am making this for soldiers. It's gotta be dummy-proof and have to work flawlessly when someone with no computer experience fills out the data.

This is why I want it to autoupdate when the sheet is selected. Based on my current status level, I'm pretty sure it can be done. It's just a matter of having a "Start macro" command at the beginning of the Code and having a "stop macro" command at the end of the code.

and, fyi (lol) Application.EnableEvent = False doesn't work either. ><

[lucas]

Your right ....application.Enablevent=False will not stop the macro. I don't think you will have much luck with a sheet activation event either because you have to go back to sheet H and that will inevitably trigger the sheet activation event again, and again.

[thomaspatton]

Well, now that you have an exact idea of what I want done, albeit many simultaneous examples, is there a way to sort the "Master Roster" like that when I select "H Roster" WITHOUT jumping back and forth? So, when I first select "H Roster" it does the sorting of "Master Roster" in the background without having to jump back and forth?

[thomaspatton]

I'm gonna go ahead and just use the Button method, rather than an autosort method. Marked : Solved.