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.

Hi,

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?

Thanks

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

Hi and thanks again. Works perfectly! Also been spending a bit of time going through the code and trying to work out what's going on.

I've reused the code several times in different workbooks/projects. So once again a big thanks for your time.

Regards

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?

Thanks

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

Wow! Worked first time :smile: thank you very much, its much appreciated.

Regards

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.

[ATTACH=CONFIG]40299[/ATTACH]

Hi can someone help please?

I have two worksheets and wish to copy rows from worksheet 1 to worksheet 2 if a condition is met in one of the cells within that row.

Hope that makes sense.

Thanks

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!)

Brilliant.

Thanks again

Hi

I've been searching but without much joy.

What I'm trying to do in vba is search for a number and then return what the row number is for that number.

Hopefully someone can help.

Many thanks

Hi

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.

Re: Combobox Keeps Multiplying Its Items Each Click

Thanks Both!

RoyUK, its all working now. Thanks very much.

Regards

Hi,

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

Private Sub ComboBox1_DropButtonClick()


With ComboBox1
    .AddItem "example 1"
    .AddItem "example 2"
    .AddItem "example 3"
End With


End Sub

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.

or

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.

Regards

Hi

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.

Dim ws As Worksheet
Dim lastRng As Range
Application.ScreenUpdating = False 'speed up code
For Each ws In ThisWorkbook.Worksheets
Set lastRng = ThisWorkbook.Sheets("PrintScn").Range("A65536").End(xlUp).Offset(1, 0)


    Select Case ws.Name
        Case "PrintScn" 'exlude
        'do nothing
        Case "Properties" 'exlude
        'do nothing
        Case "Martin's to-do list" 'exlude
        'do nothing
        Case "Main" 'exlude
        'do nothing
        Case Else
        ws.Activate
        Range("A5", Range("A65536").End(xlUp)).EntireRow.Copy lastRng
    End Select


Next
Application.CutCopyMode = False 'clear clipboard
Application.ScreenUpdating = True
Sheets("PrintScn").Activate

Re: Checking For Expired Dates In A Range

Wow, thanks Dave

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.

Regards

Dim ACell As Range


For Each ACell In Sheets("Information").Range("i7:i100")


        
        If ACell.Value > Date Then
        GoTo AA
                    
        ElseIf ACell.Value = "" Then
        GoTo AA
        
        Else
        
        UserForm5.Show
        GoTo BA
          
        End If
        
AA:
        
  Next ACell


BA:


End Sub

Hi

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

Private Sub Workbook_Open()
                                     
                      
'Check Expiry Dates


Dim ACell As Range


For Each ACell In Sheets("Information").Range("i7:i100")
        
        If ACell.Value = Date Then
        UserForm5.Show
        GoTo AA
                
        ElseIf ACell.Value < Date Then
        UserForm5.Show
        GoTo AA
        
        Else
        
        End If
  Next ACell


AA:




End Sub

Re: Updating Html Forms With Textboxes

Andrew,

Thank you so much for a detailed reply.

Yes, sorry I did mean an Edit Box (Textbox Form). I'll give this a whirl and let you know how I get on.

Thanks

Martin

Hi

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.

Thanks