Sleeper: Follow up question on "Solved" thread

[K. Georgiadis]

After I marked this solved (by XLD) , I came up with the following issue:

When the workbook is first opened, the sorting apparently does take place and functions correctly. However, the process does not appear to repeat itself each time I reopen the workbook. For example, when (a) I clear the contents in the tabs "Farming," "Hardwood," "Industrial," and "Others," (b) save the workbook, and (c) reopen it, it does not repopulate the cleared worksheets. How can I fix that?

I'm being barred from resubmitting the relevant DynamicSortZipped.zip file which was posted by XLD in the thread "Can VBA do what cannot be done with Advanced Filtering?"

[Bob Phillips]

.

[K. Georgiadis]

Thanks, this works greate in the file that you submitted but I have been struggling to adapt it to my "real file" that is much larger though similar in the structure of the relevant worksheets. So, if I may, let me ask you about the changes that you made to this new version; the ones that I noticed were

1) In worksheet "Summary" the addition of the lines:


Application.ScreenUpdating = False
Summary.Activate
Application.ScreenUpdating = True


2) In This Workbook the addition of:



Private Sub Workbook_Open()
Summary.Range("B1").Value = Summary.Range("B1").Value
End Sub


When I apply the code to my "real" file, the code in ThisWorkbook returns a compile error on the argument that follows the = sign, with the message "Variable not defined."

Other than size, the basic design of the "Summary" w/s is the same in your file and mine and "B1" contains the same information. Is there any other code elsewhere in your file that I might have missed? Why does it stumble on the argument after the "=" sign and not on the identical argument before the "+" sign?

Thanks again for your help.

[Bob Phillips]

The screenupdating was just to stop some screen flicker.

The real change is in the Workbook_Open event. Yours probably doesn't work because I changed the codename of the Summary worksheet to Summary (from Sheet2), just in case it gets renamed.

.

[K. Georgiadis]

That seems to have been the problem! Now that I changed the code name to "Summary" the "real" file seems to work too! I'll take this through its paces, in the meantime thanks and :beerchug:

[K. Georgiadis]

XLD, a quick question on workbook revision that I am considering: I want to add a "Navigation Page" and I want the cursor to be on cell B2 after opening the workbook and executing the filter and copy commands. Where should I put the command so as not to conflict with your


Summary.Activate

in the "Summary" worksheet?

[Bob Phillips]

XLD, a quick question on workbook revision that I am considering: I want to add a "Navigation Page" and I want the cursor to be on cell B2 after opening the workbook and executing the filter and copy commands. Where should I put the command so as not to conflict with your


Summary.Activate

in the "Summary" worksheet?

Just change the Workbook_Open code to


Private Sub Workbook_Open()
Summary.Range("B1").Value = Summary.Range("B1").Value
Summary.Activate
Range("B2").Select
End Sub

the worksheet event will fire before the activate and select comes into play.

.

[K. Georgiadis]

I did a lousy job of explaining my objective. After executing the filter & sort commands, I want Excel to take the user to cell B2 of the new worksheet "Navigation Page."

[Bob Phillips]

I did a lousy job of explaining my objective. After executing the filter & sort commands, I want Excel to take the user to cell B2 of the new worksheet "Navigation Page."

So just use
Worksheets("Navigation Page").Activate

instead of


Summary.Activate

[K. Georgiadis]

Strange...I added:


Worksheets("Navigation").Activate
Range("B2").Select


but the cursor goes to Navigation cell I3!

[K. Georgiadis]

Actually, each time the workbook is opened, the cursor goes to a different cell in "Navigation." I tried hiding columns A:G, hoping that the cursor would land approximately where I wanted it, but that didn't help...:banghead: