Need formula for populating info to corresponding columns in separate sheet

[kAR87]

I'm not even sure where to begin with this but here is what I was asked to do:

"When a date is entered in column F and the "Execution/Monitoring in Construction" is selected from the drop down list in column D,
the Column information for that row is populated to the corresponding column headings in the "CMI_PMT_Project In Progress" tab:
Customer Name, Project #, Project Name, Project Lifecycle Phase, Contract Award / NTP Date, Substantial Completion Date, Project Revenue, Date Last Updated/Reviewed." I've been researching and all that I've been able to find is info on VLOOKUP and array formulas. If either of these are correct I don't know how to apply them to my situation. I can provide additional information if needed! Thank you!

<colgroup><col span="2"><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[mumps]

I'm glad it actually worked for you. The PC and Mac versions of VBA are slightly different. If you open threads in the future, it would be helpful to forum members if you mention that you are using a Mac and the version of Excel.

 

[kAR87]

When I try to save the file I get a message saying that my version of Excel does not support VBA or something to that effect. Will this code still work for the version of excel that is called 'Microsoft Excel non-commercial use'? Thanks

 

[mumps]

I'm afraid that I don't have any experience working with a Mac. Try SaveAS and in the 'Save as type' box, choose "Excel macro-enabled workbook" as the file type.

 

[kAR87]

Hi there,

I was wondering if you could help me create a code doing the same exact thing for this excel doc as you did for the previous one.

https://www.dropbox.com/s/z7y60odw0...ng and Reporting System -3-26_Kelci.xlsx?dl=0


Also, is it super difficult to learn how to create the codes?

Thanks!

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Dim bottomA As Long
    bottomA = Sheets("PMT_Project In Progress").Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    If Target.Offset(0, -2) = "Execution/Monitoring - In Construction" Then
        Range("A" & Target.Row & ":C" & Target.Row).Copy Sheets("PMT_Project In Progress").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Sheets("PMT_Project In Progress").Cells(bottomA + 1, "Q") = Target
        Sheets("PMT_Project In Progress").Cells(bottomA + 1, "S") = Target.Offset(0, 2)
        Sheets("PMT_Project In Progress").Cells(bottomA + 1, "U") = Target.Offset(0, 3)
        Sheets("PMT_Project In Progress").Cells(bottomA + 1, "F") = Target.Offset(0, 6)
    End If
    Application.ScreenUpdating = True
End Sub

I found that the best way to learn VBA on your own is to use on-line tutorials, searching the web for specific areas of interest and following forums such as this one. The volunteers on sites such as this one are invaluable at providing help. I would suggest you keep a file of codes that you find useful and in this way you can build up a library that you can refer to. A lot is also trail and error. The more you practise, the better your get at it.

 

[kAR87]

Thank you so much for all your help! I'll have to check out some tutorials.

 

[mumps]

My pleasure.

 

[kAR87]

Sorry but I've got another question...

In the macro as it is now when the PMT has no information in it and it is automatically populated from the LRMT when a date was entered into the Contract Award column. See the example below for a problem:

When some of the Columns in Row 6 of PMT is empty, the automatically populated data that was supposed to go to row 7 will go to row 6. For example, Column A to D of row 6 have data but column H of row 6 doesn't have data, the data from the LRMT will be copied columns A to D to row 7 of PMT and the data from column H will be copied to row 6 of PMT. Can you fix this so all the data will be copied to the same row?

 

[mumps]

I'm sorry but I can't reproduce that problem on the copy of the file you posted. Can you post the file that has this problem. Describe it in detail using a few examples with reference to specific cells and worksheets.

 

[kAR87]

Hi there, another question with the same excel file from before if you're willing to help...

I was wondering if there was a way that I could automate an action so that when I select content from a drop down list, it triggers the content of another cell to be cleared?

Here's an example: If a bid that was submitted was unsuccessful, once I select "Unsuccessful Bids" from the drop down list (Column D of the LRMT tab), then the resources assigned to that bid should clear from the cells in another column (Column K of the LRMT tab)


Here's the link to the file:
https://www.dropbox.com/s/g6r49b7od...ng and Reporting System -3-26 Kelci.xlsm?dl=0


THANK YOU!!