Posts by Rob Xaos

    Re: Dynamic named range, ignoring blanks... but on different worksheet?

    I think (and without a current version of your Workbook to test with, I am slightly shooting in the dark here) that all you need to do is change your Data Validation to List and then use =Dates as your Source.

    If thats not working then please upload a Workbook (having removed/disguised confidential information) and someone will figure it out :)

    Re: Generate multiple worksheets from one worksheet


    Welcome to Ozgrid, and while we are pleased you have been provided with a solution to your problem, please do not pose your questions in threads started by others. Ozgrid forum rules are one question (or close follow-ups) by the Original Poster only. In future please start your own thread and if it helps explain your problem then post a link back to a previous thread.

    Thank you.

    Re: Can Excel handle repeatedly creating and destroying controls

    There are ways to create multi-page UserForms, however they cannot be 'invisible'. The whole purpose of a UserForm is to contain a set of controls that are used to modify your Workbook/Worksheets. My suggestion was to make it 'Modeless' that means you can have the UserForm open and move around Excel virtually as if the UserForm was not there. That means you can: select cells, select different Worksheets, perform almost any actions you require on the Workbook (although you need to be aware that the UserForm is there and that any of its events could be triggered by changes you make to the Workbook).

    I can understand that with 8 years invested in a project that you are reluctant to 'take a different approach' as I phrased it. However you may possibly have to accept that it has outgrown Excel. Some things Excel does exceptionally well, and in my opinion it is one of the most polished of the Office applictions, but handling very huge amounts of data for many different users (which is what this sounds like) is not one of them.

    Re: Open Word File and GoTo bookmark

    Not an expert on Word VBA by any means but the following seemed to work for me:

    Selection.GoTo What:=wdGoToBookmark, Name:="Test"

    Although normally I would shy away from using Selection/Select, since in this case you actually want the cursor to change positions then I think that is what is required.

    Since I believe there can only be one active Selection I don't think you need to qualify it with a document (in fact I seemed to get a run-time error when I tried).

    Re: Open database exclusively

    I believe it applies to any database opened in MS Access, however if there are already users connected to (for example) an SQL database then it will not give you exclusivity (since they are already connected). However if you use this on an SQL database with no users currently connected to it then it will be opened in single user mode.

    Re: Create new sheet based on cell date


    In Worksheet code module:

    Just change the address $E$4 to whichever cell you want this to activate for. The code must go in the Worksheet for that cell of course.

    Re: Autosort on different worksheet cell change

    Uploaded forumhelp.xlsm Workbook has #REF errors in the column F formulas which makes it a bit tricky to test. However I think what you need is:

    In Worksheet "New Hires" code module:

    Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

    I would agree with Stephen that Advanced Filter would be the way to go. It has all the capabilities of COUNTIF(S) criteria and more since you can create AND/OR functions. You might want to use some VBA to build the criteria but the performance of the filter will be much faster than a UDF.

    Re: Formula to sum amounts based on Today() or chosen date


    Welcome to Ozgrid, please note the change made to your thread title. Your orginal title Help with Excel Formula does not help those trying to provide you with a solution nor will it help others find this thread when it contains a solution. Please take this into consideration when you next post.

    Thank you.

    Re: Resize Table Pasted From Excel,


    Welcome to Ozgrid and thank you for posting an alternative solution in this thread. However in future please ensure that all VBA is wrapped in [noparse]


    [/noparse] tags.

    Thank you.

    Also I would recommend that you remove your email from the posted code. Public forums like Ozgrid are regularly harvested for email addresses by bots and you are likely to become the target to a great deal of spam if your email address remains.

    Re: Code to print selected sheets

    Simple but perhaps unsatisfying solution:

    Sub prnt()
        Sheets(Array(1, 3, 5, 8, 9, 10, 11, 12, 14)).PrintOut
        Sheets(1).PrintOut Copies:=2
    End Sub

    The alternative would be to take the array as a parameter and iterate through it printing each sheet individually. I am not aware of a way to have a collection return multiple instances of the same object (which is what you are asking effectively by passing an array to the Sheets collection with duplicate identifiers).

    Re: Multiple criteria lookup


    While we welcome you to Ozgrid please do not post new questions in other threads. Ozgrid rules allow one question (or close follow ups) by the original poster only. Please start your own thread and if necessary to explain your issue then link back to a previous thread.

    Also, in future please do not requote previous posts unless it is absolutely necessary to explain yourself.

    Thank you.

    Re: Count unique entries from columns in 2 sheets.


    I think you are not entering the List Range correctly, the use of Old/New/Inventory_new/Price_List_Range have become somewhat muddled as you started off refering to them only as Sheet1 and Sheet 4 so I just took a 'best guess' at what they should be called for the DNRs.

    If you take your most recent file and without doing anything else just use: Data > Filter > Advanced filter.
    Copy to another location: Selected
    List Range: Inventory_new

    Change nothing else

    Hit OK.

    You should get your list.

    Re: double quote usage with Evaluate function

    The Evaluate method only works for Excel formulas as they would be entered directly into an Excel cell. You are mixing the use of an Excel formula using COUNTA with VBA syntax. You need:

    MsgBox Evaluate("COUNTA('" & SourceWB.Worksheets(1).Name & "'!B:B)")


    MsgBox WorksheetFunction.CountA(SourceWB.Worksheets(1).Range("B:B"))

    Re: Sum through a column and group the rows found

    I'm glad you were able to find a solution. Please remember that for any future posts you make any VBA code must be wrapped in [noparse]


    [/noparse] tags.

    Also, public forums like this are constantly scanned by Bots to harvest email addresses. The person who wrote your code for you is unlikely to be pleased if their email address is added to the list of these Bots as a target for Spam. I have therefore removed the email address.