Posts by Simon Lloyd

    Re: Send Value To Specific Cell

    Providing you are only using figures:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C1:G20")) Is Nothing Then 'change to suit
    Range("A" & Target.Value).Value = Target.Row 'change to suit
    End If
    End Sub

    the above code goes in the worksheet code module you are working with (Alt+F11, then double click the sheet name on your left and paste this in!

    Re: Alternate Between 2 Macros With One Button

    Quote from AAE

    Nice job Simon. (BTW - "Option Explicit" got left out of the code tags)

    Thanks and fixed, as for the "Button" i should have stated that but there again the user can adapt the idea of the code to their particular button!

    Re: Alternate Between 2 Macros With One Button

    Try something like this:

    Re: Vb That Gives Static Time While Updating A Cell While The Sheet Is Locked

    Welcome to Ozgrid, use the code below in the worksheet module of the sheet you are using

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    If Target <> vbNullString And Target.Offset(0, 1) = vbNullString Then
    Me.Unprotect Password:="password" ' password would be the one you set for the sheet
    Target.Offset(0, 1).Value = Time
    Me.Protect Password:="password"
    End If
    End Sub

    make sure that the cells in column A are unlocked.

    Re: Save Monthly Data To New Workbook Named As Month

    This works perfect for me:

    don't forget to change the path, and remember that this is looking for the full month name not Jan but January, if you want to use Jan then remove one of the m's from

    If Application.WorksheetFunction.Text(MyCell, "mmmm") = IB Then

    Re: Save Monthly Data To New Workbook Named As Month

    Just change IB for


    , Kiz just a word....Dave is right, excel's built in functions are far more efficient and useable, to change destination or what it copies and how in code takes a more time and knowledge, than letting excel do the work for you, take a few minutes to follow daves suggestions to find out how powerful pivot tables are!

    Re: Macro To Save New File By Month Name

    You can assign thsi macro to a button, you need to change the destination path of the saved workbook, the macro will prompt you for a month enter one press ok and a new workbook will be saved to your designated path with a sheet for that month named as the original workbook plus the month!

    Dont forget to change


    "C:\Documents and Settings\USER\My Documents\

    for your actual path that you want to save it to!

    Re: Find Next Date In Range From Input

    try something like

    Sub datefind()
    Range("A1").Value = Application.WorksheetFunction. _
    VLookup(Range("H20"), Columns("D:D"), 1, True)
    'using true will find the next nearest date without going past the date
    End Sub

    Re: Find Adjacent Data And Copy

    Glad i could help, it can be modified to find anything you choose and paste next to anything you choose like this:


    Re: Find Adjacent Data And Copy

    Try this:

    Sub Macro2()
        Cells.Find(What:="Difference", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Offset(0, 1).Copy Destination:=Cells.Find(What:="Ops", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Offset(0, 1)
    End Sub

    Re: Vba Macro That Creates Copies Of A Worksheet And Transfers Data Into It And Prints It

    You need to supply workbooks showing the stucture you are using, everything you ask i fairly simple, in fact you could use the macro recorder to get you most of the way there just switch it on and perform the actions, take a look at the code which you say you can usually alter to make work for your situation and when you have something near and a workbook we can look at get back to us where we will be glad to help!

    Re: Copy Duplicated Numbers Between 2 Sheets

    This looks for a count of more than one instance of an entry in column c sheet 1 then if there is copy the entire row to sheet 2 then delete the entry and so on!, just change the sheet names to suit.

    [hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Cross posted!

    Re: Form To Seach Workbook For A Value

    I just used the code below i had a cell with two words in one of which i was looking for, in the input box i entered the word and it found it no problem, i have now fixed it so that it highlights the entire row.

    Re: Form To Seach Workbook For A Value

    That seems a lot of code just to find a value!, try this:

    run the macro, enter the word or number or whatever to look for and take it from there, you should be able to modify it!