PDA

View Full Version : Solved: Follow up question on "Solved" thread



K. Georgiadis
09-23-2005, 05:56 PM
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?"

xld
09-24-2005, 01:47 AM
.

K. Georgiadis
09-24-2005, 11:50 AM
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.

K. Georgiadis
09-24-2005, 11:53 AM
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.

xld
09-24-2005, 12:02 PM
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
09-24-2005, 12:15 PM
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
09-25-2005, 07:06 PM
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?

xld
09-26-2005, 01:28 AM
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
09-26-2005, 04:07 AM
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."

xld
09-26-2005, 05:38 AM
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
09-26-2005, 05:51 AM
Strange...I added:

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


but the cursor goes to Navigation cell I3!

K. Georgiadis
09-27-2005, 05:12 AM
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: