Copying table down multiple rows, while reference moves down only 1 row.

[MelissaBr]

Hi guys,

I am making a results file and I could use some help!

In a sheet called "Info" I can specify how many samples I have. Then, with the code below, it will copy the table until I have that amount. It will also continue the result overview on the right side.

Sub CopyMulti()

Dim amount As Long

amount = Sheets("Info").Range("C2").Value - 1
If Worksheets("Test1").Visible = True Then
Worksheets("Test1").Range("B5:P13").Copy Worksheets("Test1").Range("B14").Resize(9 * amount)
Worksheets("Test1.").Range("R6:T6").Copy Worksheets("Test1").Range("R7").Resize(1 * amount)
End If
End Sub

Let's say I have 3 samples:

TestFile.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
4
5																		Nr.	LD	CD
6		Nr.			Before	After	Result	Average		Nr.			Before	After	Result	Average		1	10.00	10.00
7		1	LD	1			0.00	5.00		1	LD	1			0.00	5.00		2	0.00	0.00
8				2			0.00					2			0.00			3	0.00	0.00
9				3			0.00					3			0.00
10			CD	1			0.00	5.00			CD	1			0.00	5.00
11				2			0.00					2			0.00
12				3			0.00					3			0.00
13
14
15		Nr.			Before	After	Result	Average		Nr.			Before	After	Result	Average
16		0	LD	1			0.00	5.00		0	LD	1			0.00	5.00
17				2			0.00					2			0.00
18				3			0.00					3			0.00
19			CD	1			0.00	5.00			CD	1			0.00	5.00
20				2			0.00					2			0.00
21				3			0.00					3			0.00
22
23
24		Nr.			Before	After	Result	Average		Nr.			Before	After	Result	Average
25		0	LD	1			0.00	5.00		0	LD	1			0.00	5.00
26				2			0.00					2			0.00
27				3			0.00					3			0.00
28			CD	1			0.00	5.00			CD	1			0.00	5.00
29				2			0.00					2			0.00
30				3			0.00					3			0.00
31
Test1

Cell Formulas
Range	Formula
R6:R8	R6	=Results!B8
S6:S8	S6	=ABS(H7)+ABS(P7)
T6:T8	T6	=ABS(H10)+ABS(P10)
B7,B25,B16	B7	=R6
J7,J25,J16	J7	=B7
G7:G12,O25:O30,G25:G30,O16:O21,G16:G21,O7:O12	G7	=+((F7-E7)/200)*100

As you can see, the references in the tables for the sample Nr.(or name) gets messed up, as well as the reference in the overview results on the right.
Is there anyone that could help me with this?

I have like 15 sheet that I need this for, but all the tables are different size wise. So if it could be adaptable for my different sheets(I don't mind changing the cells in the code manually at all), that would be amazing!

Thanks!
-Melissa

 

[MelissaBr]

Still looking for some help with this

 

[MelissaBr]

Maybe not the best way, but I figured it out using the OFFSET function.
=OFFSET($R$7;ROW()/9;0) for the numbering on the left
=ABS(OFFSET($H$1;(ROW()-7)*9;0))+ABS(OFFSET($P$1;(ROW()-7)*9;0)) for the overview on the right