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

[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/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