PLEASE! Macro If statement to copy and paste and add

[bemcbride]

So on my first tab I have general grant info. When a new grant is added that's not on my Financial tab, I added a simple Vlookup to indicate that the macro needs to run to add the grant to the financial page. That way we can easily determine if the grant needs to be added to the Financial tab or not. What I want to happen is when the Add Grant macro is run it will get the new grant and add it to the Financial tab like below highlighted Financial Tab.

Process will be: user inputs new grant data on Info tab. user clicks add grant button (which will take the new grant and add it to the financial page with each Type, again example below.)

I think that the Macro could do an "if then statement" that would read:

IF column M says "Please add grant" THEN insert 5 rows named Expenditures, Revenues, etc. and copy grant number from Info Column D (Agency Grant Number) and paste in Column D in Financials.


Info Tab:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Department
[/TD]
[TD]PP Grant Number
[/TD]
[TD]Agency Grant #
[/TD]
[TD]CFDA
[/TD]
[TD]Added
[/TD]
[/TR]
[TR]
[TD]560[/TD]
[TD]GTX000001[/TD]
[TD]TX-00-001[/TD]
[TD][/TD]
[TD]Added[/TD]
[/TR]
[TR]
[TD]560[/TD]
[TD]GTX000002[/TD]
[TD]TX-00-002[/TD]
[TD][/TD]
[TD]Added[/TD]
[/TR]
[TR]
[TD]560
[/TD]
[TD]GTX000003
[/TD]
[TD]TX-00-003
[/TD]
[TD][/TD]
[TD]Please Add Grant
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Financial tab:
[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Department
[/TD]
[TD]PP Grant Number
[/TD]
[TD]Agency Grant #
[/TD]
[TD]Sept.
[/TD]
[/TR]
[TR]
[TD]Expenditure[/TD]
[TD]560[/TD]
[TD]GTX000001[/TD]
[TD]TX-00-001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]560[/TD]
[TD]GTX000001[/TD]
[TD]TX-00-001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PI[/TD]
[TD]560[/TD]
[TD]GTX000001[/TD]
[TD]TX-00-001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Kind[/TD]
[TD]560[/TD]
[TD]GTX000001[/TD]
[TD]TX-00-001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grant Match[/TD]
[TD]560[/TD]
[TD]GTX000001[/TD]
[TD]TX-00-001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expenditure[/TD]
[TD]560[/TD]
[TD]GTX000002[/TD]
[TD]TX-00-002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]560[/TD]
[TD]GTX000002[/TD]
[TD]TX-00-002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PI[/TD]
[TD]560[/TD]
[TD]GTX000002[/TD]
[TD]TX-00-002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Kind[/TD]
[TD]560[/TD]
[TD]GTX000002[/TD]
[TD]TX-00-002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grant Match[/TD]
[TD]560[/TD]
[TD]GTX000002[/TD]
[TD]TX-00-002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expenditure
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TX-00-003[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Revenue
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TX-00-003[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PI
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TX-00-003[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]In Kind
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TX-00-003[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grant Match
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TX-00-003[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have the below code but:
1) I need to figure out a way to have it look at if Column E needs to be added and then Copy and Paste (if then)
2) It needs to also add in Expenditure, Revenue, etc.

Please excuse me, I tried!

  Sub CandP()

    Dim Last_Row1 As Long, Last_Row2 As Long
    Dim ws1 As Worksheet, ws2 As Worksheet

    Set ws1 = ActiveWorkbook.Sheets("Department Information") ' Change the name of your Sheet
    Set ws2 = ActiveWorkbook.Sheets("Financial") ' Change the name of your Sheet
If Range("L:L") = "Please add grant" Then

    Last_Row1 = ws1.Range("A" & Rows.Count).End(xlUp).Row ' Determine the lastrow of the data to copy
    Last_Row2 = ws2.Range("D" & Rows.Count).End(xlUp).Row + 1 ' Determine the next empty row in order to paste the data

    ws1.Range("C" & Last_Row1).Copy ws2.Range("D" & Last_Row2)
End If

    End Sub

 

[BQardi]

I'm not 100% sure that I am using the correct column references, but try this:

Sub CandP()
Dim wsInfo As Worksheet, wsFinancial As Worksheet
Dim FndCell As Range, DstRow As Long
Dim AgencyGrantNumber As String, PPGrantNumber As String, Department As String
Set wsInfo = ActiveWorkbook.Sheets("Department Information") ' Change the name of your Sheet
Set wsFinancial = ActiveWorkbook.Sheets("Financial") ' Change the name of your Sheet
Repeat:
On Error Resume Next
Set FndCell = wsInfo.Range("M:M").Find("please add grant", , xlValues, xlWhole)
On Error GoTo 0
If Not FndCell Is Nothing Then
    AgencyGrantNumber = FndCell.Offset(0, -9).Value
    PPGrantNumber = FndCell.Offset(0, -10).Value
    Department = FndCell.Offset(0, -11).Value
    With wsFinancial
        DstRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(DstRow, 1).Value = "Expenditure"
        .Cells(DstRow + 1, 1).Value = "Revenue"
        .Cells(DstRow + 2, 1).Value = "PI"
        .Cells(DstRow + 3, 1).Value = "In Kind"
        .Cells(DstRow + 4, 1).Value = "Grant Match"
        .Range("D" & DstRow & ":D" & DstRow + 4).Value = AgencyGrantNumber
        .Range("C" & DstRow & ":C" & DstRow + 4).Value = PPGrantNumber
        .Range("B" & DstRow & ":B" & DstRow + 4).Value = Department
    End With
    FndCell.Value = "Added"
    GoTo Repeat
End If
End Sub