Need a Macro

[Sufiyan97]

Is it possible?
I need a macro to fill data in different tabs of a worksheet

In below Input sheet I will input Investment name and all other info. like shown below, and then I will delete it from Input tab, but it should remain on Investment tab

Green Banana Investment Summary.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Select Investment	Date	Amount	Purpose	Transaction Type	ROI	Annual ROI	Annual Receipts	Notes	Notes
2	Hillside West 1100	08/13/2015	$ 100,000.00		Check
3	Hillside West 1100	02/13/2017	$ 2,967.19		Deposit	2.97%
4	Hillside West 1100	05/09/2017	$ 2,393.13		Deposit	2.39%
5	Hillside West 1100	08/10/2017	$ 1,618.75		Deposit	1.62%
6	Hillside West 1100	11/08/2017	$ 2,482.27		Deposit	2.48%	9.46%	$ 9,461.34
Input

Cell Formulas
Range	Formula
C3	C3	=2967.19
F3:F6	F3	=+C3/$C$2
G6	G6	=SUM(F3:F6)
H6	H6	=SUM(C3:C6)

In column A when I put Investment name and all other info it should copied to the that investment tab



Below is an investment tab macro should find last filled row and paste data to next blank row.

Green Banana Investment Summary.xlsx
	A	B	C	D	E	F	G	H
3	Date	Amount	Purpose	Transaction Type	ROI	Annual ROI	Annual Receipts	Notes
4	08/13/2015	$ 100,000.00		Check
5	02/13/2017	$ 2,967.19		Deposit	2.97%
6	05/09/2017	$ 2,393.13		Deposit	2.39%
7	08/10/2017	$ 1,618.75		Deposit	1.62%
8	11/08/2017	$ 2,482.27		Deposit	2.48%	9.46%	$ 9,461.34
9	02/06/2018	$ 2,316.79		Deposit	2.32%
10	05/08/2018	$ 2,231.25		Deposit	2.23%
11	08/01/2018	$ 3,756.50		Deposit	3.76%
12	10/31/2018	$ 2,813.24		Deposit	2.81%	11.12%	$ 11,117.78
13	01/29/2019	$ 1,334.38		Deposit	1.33%
14	04/11/2019	$ 55,485.51		Deposit	55.49%
15	04/29/2019	$ 2,978.72		Deposit	2.98%
16	07/22/2019	$ 3,281.25		Deposit	3.28%
17	11/06/2019	$ 2,482.27		Deposit	2.48%	65.56%	$ 65,562.13
18	02/13/2020	$ 1,654.85		Deposit	1.65%
19	07/24/2020	$ 4,856.25		Deposit	4.86%
20	10/21/2020	$ 2,978.72		Deposit	2.98%	9.49%	$ 9,489.82
21	10/22/2020	$ 2.00	2	2	0.00%	2.00%	2
Hillside West 1100

Cell Formulas
Range	Formula
B5	B5	=2967.19
F8,F12	F8	=SUM(E5:E8)
G8,G12	G8	=SUM(B5:B8)
F17	F17	=SUM(E13:E17)
G17	G17	=SUM(B13:B17)
F20	F20	=SUM(E18:E20)
G20	G20	=SUM(B18:B20)
E5:E21	E5	=+B5/$B$4

Below is another Investment tab

Green Banana Investment Summary.xlsx
	A	B	C	D	E	F	G	H
3	Date	Amount	Purpose	Transaction Type	ROI	Annual ROI	Annual Recipts	Notes
4	12/31/2015	150,000.00		Journal Entry				Investment made via partners personal accounts ($50K each)
5	02/13/2017	5,592.98		Deposit	3.73%
6	05/09/2017	5,592.98		Deposit	3.73%
7	08/02/2017	5,066.83		Deposit	3.38%
8	08/28/2017	139,740.94		Deposit	93.16%
9	11/08/2017	6,440.40		Deposit	4.29%	108.29%	$ 162,434.13
10	02/06/2018	4,412.53		Deposit	2.94%
11	04/27/2018	2,568.52		Deposit	1.71%
12	07/30/2018	2,001.05		Deposit	1.33%
13	10/31/2018	1,756.23		Deposit	1.17%	7.16%	$ 10,738.33
14	01/29/2019	2,118.94		Deposit	1.41%
15	05/08/2019	1,772.31		Deposit	1.18%
16	07/26/2019	1,772.31		Deposit	1.18%
17	10/24/2019	1,883.08		Deposit	1.26%	5.03%	$ 7,546.64
18	02/13/2020	2,603.08		Deposit	1.74%
19	06/12/2020	2,215.38		Deposit	1.48%
20	09/08/2020	1,550.77		Deposit	1.03%
21	10/29/2020	1,107.69		Deposit	0.74%	4.98%	$ 7,476.92
22	10/29/2020	1,107.69		Deposit	0.74%	3.99%	$ 5,981.53
23	10/29/2020	1.00	1	1	1.00%	1.00%	$ 1.00
24	10/30/2020	20.00	2	2	0.01%	2.00%	$ 2.00
25	10/31/2020	50.00	55	58	0.03%	59.00%	$ 60.00	61
Hallmark West 1101

Cell Formulas
Range	Formula
F9	F9	=SUM(E5:E9)
G9	G9	=SUM(B5:B9)
F13,F21:F22,F17	F13	=SUM(E10:E13)
G13,G21:G22,G17	G13	=SUM(B10:B13)
E24:E25,E5:E22	E5	=+B5/$B$4

 

[mart37]

Why a macro? You can use a Pivot Table.

 

[Sufiyan97]

How pivot will update all sheet tabs??

 

[mart37]

Keep all your data on sheet INPUT. Format your data as a table (Format as Table).
Make on the sheets with a SELECT INVESTMENT a pivot table.
When there is new data added on the sheet INPUT you can refresh all pivot tables in once.

It is also possible to make from one pivot table all the other sheets.
In the pivot table you place SELECT INVESTMENT in the section FILTERS.
Now you can create sheets for every SELECT INVESTMENT with the option: Show Report Filter Pages

 

[Sufiyan97]

Keep all your data on sheet INPUT. Format your data as a table (Format as Table).
Make on the sheets with a SELECT INVESTMENT a pivot table.
When there is new data added on the sheet INPUT you can refresh all pivot tables in once.

It is also possible to make from one pivot table all the other sheets.
In the pivot table you place SELECT INVESTMENT in the section FILTERS.
Now you can create sheets for every SELECT INVESTMENT with the option: Show Report Filter Pages

Thank you very much mart
I did not think of pivot table earlier.

 

[Sufiyan97]

Hello Mart,

I am facing an issue in show report filter pages in pivot table,

when I add new record in input sheet (New Investment) and then again when I apply show report filter pages it created duplicates of all investment tabs, I want only new investment added as new tab and not all repeated which are already there.

In below screenshot I have added new Investment PG Portfolio 1103.

 

[mart37]

For method 2 you have to delete all the sheets first.
For method 1 you can refresh with one click if your table is formatted as table!