Posts by stickyfeet

    Re: If a cell meets a condition, copy that row to another worksheet

    Hi, hoping LeastAction is around.

    I'm trying to do something similar to the above by reusing your code. I'm stuggling with it :( so was hoping you can help again.

    Same kind of worksheet but there is a number in Column 'S' and when that number is met (users selects) then I need that row copied across to a new worksheet.

    I kind of (well I think) I understand bits of the code but getting it to look in 'S' just doesnt seem to work.

    Big thanks!

    Re: Delete part of hyperlink (for all hyperlinks in a worksheet)

    Hi yes it works thanks.

    Apologise for my lack of knowledge, should the For and Next statements step through to the last hyperlink?

    At the moment I have place the code into an active x button and I was expecting one click to do the lot. However I have to keep doing multiple clicks.

    Thanks again.


    Hoping someone can come to the rescue as I've recently moved a lot of my data from an external drive onto the PC hard drive.

    All links from within my worksheet no longer work :( I've Googled but nothing seems to match what I need so I have been doing it manually (another 600 entries to go!)

    It seems if I remove "..\..\..\AppData\Roaming\Microsoft\Excel\" from the existing hyperlink, then all works fine. But this is now time consuming considering the amount I have left to do.

    Does anyone know a way of removing "..\..\..\AppData\Roaming\Microsoft\Excel\" from all hyperlinks within a worksheet automatically?


    Re: If a cell meets a condition, copy that row to another worksheet

    This code is working perfectly, so much so that I'm trying to apply it to another workbook.

    I'm trying to work through it to try and determine what is going on, but I'm not getting very far.

    So I'm going to be cheeky and ask for a little more help please.

    Rather than looking at the first column, I'm trying to get the code to look at another column. Then do exactly the same thing by copying the whole line.

    I've been looking at 'lLocation' as this is where it gets the value from the combobox, then trying to work out how/where it determines which column to look at.

    Can you help please?


    Re: If a cell meets a condition, copy that row to another worksheet

    Hoping this explains a little better (attached an example workbook) and someone can help.

    • Basically worksheet 1 (Details Sheet) gets periodically updated with rows.
    • A user can then generate a report which is worksheet 2 by clicking the 'Generate Report' button.
    • When the button is clicked I would like the first column to be searched for either 1, 2, 3, or 4 (a combobox handles this)
    • When this condition is met, that row then gets copied across to worksheet 2 (Report Sheet)
    • The whole action starts when the 'Create Report' button is clicked on the userform

    Hope this explains a bit better.

    Many thanks for looking.


    Re: Find Value And Return Row Number

    Gents thanks for such quick replies (this place really is the best).

    Spoilt for choice!

    Dave and Trebor both worked a treat. Bill didnt get the chance to try yours, but a thanks all the same .... I was struggling trying to find just the one way to get this to work (and spend a long time searching and trying different things!)


    Thanks again



    Does anyone know of a way to link individual Outlook emails into Excel?

    I can do this with MS Word, by just dragging the email onto the page (where it creates a clickable icon linking to that email) ... however doing this in with Excel just means certain cells being populated with the To, From and Subject details etc.

    For info If I was to drag an icon which I have already done in Word, into an Excel worksheet a clickable Icon appears (a bit long winded)

    Hope this explains.

    Many thanks.


    This must be a simple reason, but I can't work it out. I hope someone can help.

    I have Combobox's on various forms. Each has a number of Items ... here's an example of the code

    Each time I click on the drop down the number of items repeat, ie double. This goes on and on.

    Do I have to clear the combobox? if so where is it best to do this.


    Do I have to put it in another action ie, enter or dblclick. (I have tried but I'm having no luck)

    Many Thanks

    Re: Remove Some Cell Colors After Paste

    Hi Dave

    Thanks for the reply.

    I've tried the new code, but I dont think I may have explained enough.

    The code runs through fine. But only the first cell of the row turns white. The rest stay yellow.

    Also the worksheets the users update may have multiple lines of data which move/merge to the 'PrintScn' worksheet. Where this has happened only the first cell of the first row turns white. The rows immediately below all stay yellow.

    I hope this explains. I'd be very grateful if you can have another look.



    Hoping someone can help me out here (again) :)

    This maybe a bit long winded so I hope I can explain it clearly.

    I have a number of worksheets in my workbook. Only five of these are visable to the user.

    The user can update/edit rows in these visable worksheets.

    I then have a button on the main worksheet with code that collates the rows with values from the five different worksheets. (The very first two rows copied are headers which are constant. Just copied for easy reference for the user when looking at the data in the collated sheet. Row 3 onwards contains data which the user had previously been able to change/add)

    For info, the worksheet which finally has all the collated info is called 'PrintScn'

    When the code finishes doing its bits the 'PrintScn' worksheet appears on the screen with a print button.

    All this works very nice. My problem being is .....

    The rows of data which get collated have a background colour of Yellow. For printing purposes I'd like the background colour of the rows on the PrintScn to be white ... bearing in mind the first two rows copied from any of the visable worksheets must retain their original colour (for info they are Blue)

    Here's the code I currently use (incidentally its code I found on this fantastic site)

    Thanks for looking and thanks for helping.

    Re: Checking For Expired Dates In A Range

    Thanks Charles,

    I just changed it to the following before I had a chance to read your post. Which also seems to work.

    I'll try your way and stick with that (as you can tell by my code, I'm not a coder)


    Thanks for your help.



    I currently have some code which checks the dates in a range when the workbook first opens.

    If the date in the range matches the current date or is older than the current date then I'd like a Userform to popup warning the user that something has expired.

    I'd like to add sometimes that some of the cells in the range may be blank.

    Currently the Userform pops up regardless :(

    I hope someone can help :)

    Here's what I currently have ..... Thanks


    Is it possible to populate textboxes within excel then submit them to a HTML page containing forms?

    I know it sounds long winded but users are currently duplicating their work by having to put times in an excel spreadsheet (textboxes) and then use forms on an intranet page for another dept to see.

    I was thinking that if possible it would be better to do it all from excel.

    Any advice is greatly appreciated.