Simple Macro to Copy/Paste Cells onto Another Sheet

[k3yn0t3]

Hi everyone, Can anyone give me a hand with a macro that will run and copy the results generated on one sheet onto another sheet as values? xl2bb sheets below to illustrate if helpful

I need it to...

1. Enter a “1” into cell D108 on “OnOff” sheet
2. Refresh entire workbook
3. Copy and paste *as values* the results on “Summary” sheet cells C8:P18 onto “Output tab”
   1. The destination cell on “output” tab—where I need to paste C8—should be one cell to the left right of the Scenario # input into OnOff cell D108
4. Repeat the above, but enter “2” into D108
5. Repeat, but enter “3” into D108

Thank you very much in advance.

Regards,
Sam

"OnOff" Sheet

Factories_v65.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
108	Scenario Chooser	2
109	Include in Total On / Off Switch	1
110		Factory 1	Factory 2	Factory 3	Factory 4	Factory 5	Factory 6	Factory 7	Factory 8	Factory 9	Factory 10	Factory 11	Factory 12	Factory 13	Factory 14	Factory 15	Factory 16	Factory 17	Factory 18
111	Scenario 1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
112	Scenario 2	0	0	0	1	0	1	1	1	1	1	1	1	1	0	1	1	1	1
113	Scenario 3	1	1	1	1	0	1	0	0	0	0	0	0	0	0	1	1	1	1
OnOff

Cell Formulas
Range	Formula
D109	D109	=+OFFSET(D109,$D$108,)

"Summary" Sheet

Factories_v65.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
8						2016	2017	2018	2019	2020	2021	2022	2023	2024
9	Products Manufactured					10,000,000	10,010,000	10,020,000	10,030,000	10,040,000	10,050,000	10,060,000	10,070,000	10,080,000
10
11	Revenue					500,000	550,000	600,000	650,000	700,000	750,000	800,000	850,000	900,000
12	Cost of Goods Sold					(100,000)	(110,000)	(120,000)	(130,000)	(140,000)	(150,000)	(160,000)	(170,000)	(180,000)
13	Gross Profit					400,000	440,000	480,000	520,000	560,000	600,000	640,000	680,000	720,000
14	OpEx					(100,000)	(100,000)	(100,000)	(100,000)	(100,000)	(100,000)	(100,000)	(100,000)	(100,000)
15	Overhead					(20,000)	(20,200)	(20,400)	(20,600)	(20,800)	(21,000)	(21,200)	(21,400)	(21,600)
16	Rent, Utilities					(20,000)	(20,000)	(20,000)	(20,000)	(20,000)	(20,000)	(20,000)	(20,000)	(20,000)
17	EBITDA					260,000	299,800	339,600	379,400	419,200	459,000	498,800	538,600	578,400
Summary

Cell Formulas
Range	Formula
I8:P8	I8	=+H8+1
I9:P9	I9	=+H9+10000
I11:P11	I11	=+H11+50000
H12,H14:P14	H12	=+-100000
I12:P12	I12	=+H12-10000
H13:P13	H13	=+H12+H11
H15:H16,I16:P16	H15	=+-20000
I15:P15	I15	=+H15-200
H17:P17	H17	=+SUM(H13:H16)

"Output" Tab

Factories_v65.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
2	1			-2016	-2017	-2018	-2019	-2020	-2021	-2022	-2023	-2024
3		Products Manufactured		#########	#########	#########	#########	#########	#########	#########	#########	#########
4
5		Revenue		(500,000)	(550,000)	(600,000)	(650,000)	(700,000)	(750,000)	(800,000)	(850,000)	(900,000)
6		Cost of Goods Sold		100,000	110,000	120,000	130,000	140,000	150,000	160,000	170,000	180,000
7		Gross Profit		(400,000)	(440,000)	(480,000)	(520,000)	(560,000)	(600,000)	(640,000)	(680,000)	(720,000)
8		OpEx		100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000
9		Overhead		20,000	20,200	20,400	20,600	20,800	21,000	21,200	21,400	21,600
10		Rent, Utilities		20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000
11		EBITDA		(260,000)	(299,800)	(339,600)	(379,400)	(419,200)	(459,000)	(498,800)	(538,600)	(578,400)
12
13
14	2			-2016	-2017	-2018	-2019	-2020	-2021	-2022	-2023	-2024
15		Products Manufactured		#########	#########	#########	#########	#########	#########	#########	#########	#########
16
17		Revenue		(500,000)	(550,000)	(600,000)	(650,000)	(700,000)	(750,000)	(800,000)	(850,000)	(900,000)
18		Cost of Goods Sold		100,000	110,000	120,000	130,000	140,000	150,000	160,000	170,000	180,000
19		Gross Profit		(400,000)	(440,000)	(480,000)	(520,000)	(560,000)	(600,000)	(640,000)	(680,000)	(720,000)
20		OpEx		100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000
21		Overhead		20,000	20,200	20,400	20,600	20,800	21,000	21,200	21,400	21,600
22		Rent, Utilities		20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000
23		EBITDA		(260,000)	(299,800)	(339,600)	(379,400)	(419,200)	(459,000)	(498,800)	(538,600)	(578,400)
24
25	3			-2016	-2017	-2018	-2019	-2020	-2021	-2022	-2023	-2024
26		Products Manufactured		#########	#########	#########	#########	#########	#########	#########	#########	#########
27
28		Revenue		(500,000)	(550,000)	(600,000)	(650,000)	(700,000)	(750,000)	(800,000)	(850,000)	(900,000)
29		Cost of Goods Sold		100,000	110,000	120,000	130,000	140,000	150,000	160,000	170,000	180,000
30		Gross Profit		(400,000)	(440,000)	(480,000)	(520,000)	(560,000)	(600,000)	(640,000)	(680,000)	(720,000)
31		OpEx		100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000
32		Overhead		20,000	20,200	20,400	20,600	20,800	21,000	21,200	21,400	21,600
33		Rent, Utilities		20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000
34		EBITDA		(260,000)	(299,800)	(339,600)	(379,400)	(419,200)	(459,000)	(498,800)	(538,600)	(578,400)
Output

Cell Formulas
Range	Formula
A14	A14	=+A2+1
A25	A25	=+A14+1

 

[kevin9999]

Please try the following on a copy of your workbook. Just watch the destination rows.

Option Explicit
Sub Simple_Copy()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Set ws1 = Worksheets("OnOff")
    Set ws2 = Worksheets("Summary")
    Set ws3 = Worksheets("Output")
    Dim rngSrc As Range, rngNum As Range, i As Long, n As Long
    Set rngSrc = ws2.Range("C8:P18")
    Set rngNum = ws1.Range("D108")
    Dim a(): a = Array(1, 2, 3)
    Dim b(): b = Array(2, 14, 25)       '<-- *** Check these destination row numbers ***
    
    For i = LBound(a) To UBound(a)
        rngNum.Value = a(i)
        ws2.Calculate
        rngSrc.Copy ws3.Cells(b(n), 2)
        n = n + 1
    Next i
End Sub

 

[kevin9999]

Sorry, just noticed your requirement to paste values...

Option Explicit
Sub Simple_Copy()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Set ws1 = Worksheets("OnOff")
    Set ws2 = Worksheets("Summary")
    Set ws3 = Worksheets("Output")
    Dim rngSrc As Range, rngNum As Range, i As Long, n As Long
    Set rngSrc = ws2.Range("C8:P18")
    Set rngNum = ws1.Range("D108")
    Dim a(): a = Array(1, 2, 3)
    Dim b(): b = Array(2, 14, 25)       '<-- *** Check these destination row numbers ***
    
    For i = LBound(a) To UBound(a)
        rngNum.Value = a(i)
        ws2.Calculate
        rngSrc.Copy
        ws3.Cells(b(n), 2).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        n = n + 1
    Next i
End Sub