VBA to create formula references and values in Sheet2 that either reference or are derived from Sheet1

I hope someone is willing to assist me with a macro that needs to allow the user to selected a single cell in column "C" (Item #) in "Sheet1", the macro will then create a new row on "Sheet2" one line above the last used row and fill in the cells as follows:

Create formulas in columns "A" ("Description") & "B" ("Item #") in "Sheet2" that have formulas that references those values from "Sheet1".

Then create a formula in column "E" ("Gross Income") in "Sheet2".

And lastly fill in a value in column "G" ("Sugg. Retail Price") in "Sheet2" from the value in column "F" ("Sugg. Retail Price") of "Sheet1".

Thanks so much in advance.

Hello frisbee,
Welcome to ExcelFox

I have made a start on a VBA solution for you.
( But note that handling a table such as yours often seems to be done efficiently using Excel Tables. I personally do not understand anything about those, but it might be worth you researching into that as another option. ( https://www.thespreadsheetguru.com/b...t-excel-tables ) )

I have included an extra test macro, which you can run to test the main macro. The extra macro simulates selecting range B10 in worksheet “Sheet1”

Here is a summary of the results....

Your file Before:

_____ Workbook: Spreadsheet1.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H
9	N/A	Item #6	62234A	25.00	5	125.00	2020
10	N/A	Item #6	94749A	25.00	5	125.00	2020
11

Worksheet: Sheet1

_____ Workbook: Spreadsheet1.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H
11	Item #4	F5905A	1	19.99	19.99		25.00
12	Item #5	F5922A	1	14.99	14.99		25.00
13	Totals:		13		244.87
14

Worksheet: Sheet2


After , for example selecting cell B9 from the first worksheet, "Sheet1"

_____ Workbook: Spreadsheet1.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H
11	Item #4	F5905A	1	19.99	19.99		25.00
12	Item #5	F5922A	1	14.99	14.99		25.00
13	Item #6	62234A			0.00		25.00
14	Totals:		13		244.87
15

Worksheet: Sheet2

_____ Workbook: Spreadsheet1.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H
11	=Sheet1!B7	=Sheet1!C7	1	19.99	=C11*D11		25.00
12	=Sheet1!B8	=Sheet1!C8	1	14.99	=C12*D12		25.00
13	=Sheet1!B9	=Sheet1!C9			=C13*D13		25.00
14	Totals:		=SUM(C4:C12)		=SUM(E4:E12)
15

Worksheet: Sheet2


macros here: ( http://www.excelfox.com/forum/showth...ll=1#post13158 ) and also in the first worksheet code module of uploaded returned file


Alan