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

[frisbee]

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.

[DocAElstein]

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/blog/2014/6/20/the-vba-guide-to-listobject-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/AItem #6
62234A
25.00
5
125.00
2020


10
N/AItem #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

11Item #4F5905A
1
19.99
19.99
25.00


12Item #5F5922A
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

11Item #4F5905A
1
19.99
19.99
25.00


12Item #5F5922A
1
14.99
14.99
25.00


13Item #662234A
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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13158&viewfull=1#post13158 ) and also in the first worksheet code module of uploaded returned file


Alan