Two questions, insert time if another cell, data to another sheet

[PhilExcel]

Hi,


At work we have Office 2013. I use it to keep records of my work.


There are seven columns. The first is where I enter the claim number. The second and third are columns with drop down lists to add actions. The fourth is text where I put what I did, The fifth is the time, the sixth is a drop down list for action taking and the seventh is for extra text notes.


The first of the two things I’d like to do is when the third column has data entered into it from the drop down list it will automatically add the time to the fifth column.


The sixth column has four items in the drop down. This is a list on how I went about to complete the work. Simple text, ‘Call’, ‘WEB’, ‘REV’, ‘ACT’. The final one’ ACT’ would be null for the next action.


This action is to move simple information to another sheet in the workbook. All I need is information from that days work. There would be two columns in the second sheet grabbing the claim numbers from the first column. On the second sheet the first column would be for claim numbers that were indicated with ‘Call’, the second column would be for ‘WEB’ or ‘REV’.


To run whatever action required could be simply enough by me selecting the area that was worked on the day.


Thanks in advance - Phil

 

[JLGWhiz]

This is event code and should be copied and pasted into the worksheet code moduile for the source sheet with the drop down boxes. Be sure the workbook is saved as a macro enabled workbook to preserve the code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Columns(3)) Is Nothing Then
        Target.Offset(, 2) = Time
    End If
    If Not Intersect(Target, Columns(4)) Is Nothing Then
        If LCase(Target.Value) = "call" Then
            Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2) = Target.Offset(, -3).Value
        ElseIf UCase(Target.Value) = "REV" Or UCase(Target.Value) = "WEB" Then
            Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp)(2) = Target.Offset(, -3).Value
        End If
    End If
Application.EnableEvents = True
End Sub

 

[PhilExcel]

Thank you, I added the module into Sheet 1. I then saved it as macro enabled. When I select macros - all workbooks, it doesn't appear?

 

[JLGWhiz]

Thank you, I added the module into Sheet 1. I then saved it as macro enabled. When I select macros - all workbooks, it doesn't appear?

It is not a public procedure and therefore does not appear in the list of macros in the macro dialogue box. It is an event procedure which is triggered to run by any changes you make to the host worksheet. It will only execute the copy action if you make changes in column D. It will post your time if you make changes in column C. Other than that, you should not notice any actions from any other changes as a result of this procedure.

I just noticed I used the wrong column. Delete the other code and use the one below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Columns(3)) Is Nothing Then
        Target.Offset(, 2) = Time
    End If
    If Not Intersect(Target, Columns(6)[COLOR=#b22222]) [/COLOR]Is Nothing Then
        If LCase(Target.Value) = "call" Then
            Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2) = Target.Offset(, -5).Value
        ElseIf UCase(Target.Value) = "REV" Or UCase(Target.Value) = "WEB" Then
            Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp)(2) = Target.Offset(, -5).Value
        End If
    End If
Application.EnableEvents = True
End Sub

 

[PhilExcel]

Sorry for the ignorance. I added the new code but nothing is happening. I added the code to the module, first to sheet1, then I tried 'Thisworkbook'.

 

[JLGWhiz]

Sorry for the ignorance. I added the new code but nothing is happening. I added the code to the module, first to sheet1, then I tried 'Thisworkbook'.

I don't know which code you are using. The first one I posted had the wrong column number referenced. The revised code in post #4 should work. The code is to be copied and pasted into the sheet code module of the worksheet that has the data to be copied on it, Right click that worksheet name tab and then click 'View Code' in the pop up menu. Copy and paste the code in post #4 (don't retrype) to the large code pane. Check in the margin of the VB editor window at the top to be sure it is the correct code module. You should see something like [sheetname(code)] that tells you which module you have displayed. Make sure you are not in design mode when you try to run the macro. If the design mode icon (triangle with ruler and pencil) is highlighted, click on it to remove the highlight and exit design mode. Make sure your workbook is a macro enabled workbook and that your security settings allow you to access VBA and run macros. The code ran without error in test set up.

The code only executes if changes are made in column 3 or 6.

 

[PhilExcel]

Okay, it's working! Thanks!

I did enter the second code yesterday. Anyway I started over again, deleting and reentering the code. I carefully followed your steps. Could the issue be that when I first entered the code, the workbook wasn't already saved as macro enabled? Yesterday after entering the code I saved as macro enabled. So of course this morning when opening it, it was.

Considering my Lead at work doesn't even know how to add a drop down list this might be something to share with my co-workers. This is not only time saving but educational and fun! THANK YOU!

 

[JLGWhiz]

Okay, it's working! Thanks!

I did enter the second code yesterday. Anyway I started over again, deleting and reentering the code. I carefully followed your steps. Could the issue be that when I first entered the code, the workbook wasn't already saved as macro enabled? Yesterday after entering the code I saved as macro enabled. So of course this morning when opening it, it was.

Considering my Lead at work doesn't even know how to add a drop down list this might be something to share with my co-workers. This is not only time saving but educational and fun! THANK YOU!

I have no way of knowing what might have caused your problem yesterday, but I suspect it was that you were in design mode when you tried to run the macro or you had it in the wrong code module. When a procedure begins with the word Private and it is in compliance with the Excel VBA conventions, it would be for use in either the worksheet, workbook or UserForm code modules and not one of the public code modules which are all numbered modules. Private Subs are usually event codes that rely on a click or action event to trigger them, and they will not work outside their designated environments.

 

[PhilExcel]

In sheet 2 with the two columns, can extra code to the existing one or new code be made so that when sheet2 columnA receives data, today's date would generate in Sheet2 columnC?

 

[JLGWhiz]

In sheet 2 with the two columns, can extra code to the existing one or new code be made so that when sheet2 columnA receives data, today's date would generate in Sheet2 columnC?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Columns(3)) Is Nothing Then
        Target.Offset(, 2) = Time
    End If
    If Not Intersect(Target, Columns(6)) Is Nothing Then
        If LCase(Target.Value) = "call" Then
            With Sheets("Sheet2")
                .Cells(Rows.Count, 1).End(xlUp)(2) = Target.Offset(, -5).Value
                .Cells(Rows.Count, 1).End(xlUp).Offset(, 2) = Date
            End With
        ElseIf UCase(Target.Value) = "REV" Or UCase(Target.Value) = "WEB" Then
            Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp)(2) = Target.Offset(, -5).Value
        End If
    End If
Application.EnableEvents = True
End Sub