Macro to add new rows if new cell added

[bemcbride]

OK so I have a sheet that has a list of grants and basic info on those grants (Tab Info). On another sheet (Tab Financial) I have Expenditures, Revenue, and Match in column A and Months across 9. I'm trying to have a new row inserted for each section for each grant. I want to be able to run a macro that will add 3 new row for each Type (Expenditure, Revenue, etc.) at the end of sheet for the new grant added.

Essentially the Macro would say (i think): Find last row on Info tab in Column A and on tab Financial Insert 3 rows, in new rows in Column A say Expenditure, row 2 Revenue, row 3 Match and in Column B say New Grant number.

I hope that makes sense...I thought this would be simple but i'm spinning my wheels here.


ON Info Tab:

Grant 1
Grant 2



ON Financial Tab:
Expenditure Grant 1
Revenue Grant 1
Match Grant 1
Expenditure Grant 2
Revenue Grant 2
Match Grant 2
Expenditure NEW GRANT
Revenue NEW GRANT
Match NEW GRANT

 

[cpatel13]

Re: Macro to add new rows if new cell added - HELP Please

OK so I have a sheet that has a list of grants and basic info on those grants (Tab Info). On another sheet (Tab Financial) I have Expenditures, Revenue, and Match in column A and Months across 9. I'm trying to have a new row inserted for each section for each grant. I want to be able to run a macro that will add 3 new row for each Type (Expenditure, Revenue, etc.) at the end of sheet for the new grant added.

Essentially the Macro would say (i think): Find last row on Info tab in Column A and on tab Financial Insert 3 rows, in new rows in Column A say Expenditure, row 2 Revenue, row 3 Match and in Column B say New Grant number.

I hope that makes sense...I thought this would be simple but i'm spinning my wheels here.


ON Info Tab:

Grant 1
Grant 2



ON Financial Tab:
Expenditure Grant 1
Revenue Grant 1
Match Grant 1
Expenditure Grant 2
Revenue Grant 2
Match Grant 2
Expenditure NEW GRANT
Revenue NEW GRANT
Match NEW GRANT

Hie bemcbride ,

Thanks, could you please elaborate it more please it's bit confusing, so far as i am understanding as soon as a new grant is inserted in the grant info sheet, for instance Grant NEW you want that it should create rows in financial sheet named expenditure Grant NEW revenue Grant NEW match Grant NEW. is that you want? also please send a screenshot or a sample file to make it more easy.

Regards,
Cpatel13

 

[bemcbride]

Re: Macro to add new rows if new cell added - HELP Please

Sorry about that. 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. 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.

Process will be: user inputs new grant data. user clicks add grant button (which will take the new grant and add it to the financial page with each Type.)


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:

Department	PP Grant Number	Agency Grant #	CFDA	Added
560	GTX000001	TX-00-001		Added
560	GTX000002	TX-00-002		Added
560	GTX000003	TX-00-003		Please Add Grant

<tbody>
</tbody>

Financial tab:

Type	Department	PP Grant Number	Agency Grant #	Sept.
Expenditure	560	GTX000001	TX-00-001
Revenue	560	GTX000001	TX-00-001
PI	560	GTX000001	TX-00-001
In Kind	560	GTX000001	TX-00-001
Grant Match	560	GTX000001	TX-00-001
Expenditure	560	GTX000002	TX-00-002
Revenue	560	GTX000002	TX-00-002
PI	560	GTX000002	TX-00-002
In Kind	560	GTX000002	TX-00-002
Grant Match	560	GTX000002	TX-00-002
Expenditure			TX-00-003
Revenue			TX-00-003
PI			TX-00-003
In Kind			TX-00-003
Grant Match			TX-00-003

<tbody>
</tbody>

 

[cpatel13]

Re: Macro to add new rows if new cell added - HELP Please

Sorry about that. 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. 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.

Process will be: user inputs new grant data. user clicks add grant button (which will take the new grant and add it to the financial page with each Type.)


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:

Department	PP Grant Number	Agency Grant #	CFDA	Added
560	GTX000001	TX-00-001		Added
560	GTX000002	TX-00-002		Added
560	GTX000003	TX-00-003		Please Add Grant

<tbody>
</tbody>

Financial tab:

Type	Department	PP Grant Number	Agency Grant #	Sept.
Expenditure	560	GTX000001	TX-00-001
Revenue	560	GTX000001	TX-00-001
PI	560	GTX000001	TX-00-001
In Kind	560	GTX000001	TX-00-001
Grant Match	560	GTX000001	TX-00-001
Expenditure	560	GTX000002	TX-00-002
Revenue	560	GTX000002	TX-00-002
PI	560	GTX000002	TX-00-002
In Kind	560	GTX000002	TX-00-002
Grant Match	560	GTX000002	TX-00-002
Expenditure			TX-00-003
Revenue			TX-00-003
PI			TX-00-003
In Kind			TX-00-003
Grant Match			TX-00-003

<tbody>
</tbody>

Hie,
so far as i understand from what you are describing in the table.
1) you want a button on the info sheet where once you fill C1,C2,C3 in this example and press the button it creates five types in the financial sheet and then copies the C3 from the info sheet which is agency grant number and then paste it in the financial sheet. is that all?
2) do you want the department and grant number also to be copied?

i hope i am clear, i have almost made the code, let me know.

Regards,
Cpatel13