Macro to copy and paste cells based on the result in a single cell

[saraapple]

I have a worksheet with 10 small pivot tables that show data depending on the input (the more data the more tables will be populated).
I have a formula to calculate how many tables will be in use and I know which cells I could need to copy depending on that........how do I feed this information into a macro to make the correct copy selection?

NEW WAVE FILE TRUNKS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	LD	LOAD 1	20:00:00		LD	LOAD 2	22:24:00		LD	LOAD 3	00:48:00		LD	LOAD 4	03:12:00		LD	LOAD 5	05:36:00		LD	LOAD 6	08:00:00		LD	LOAD 7	10:24:00		LD	(blank)		LD	(blank)		LD	(blank)		NUMBER OF LOADS	7
2	PRIORITY	(Multiple Items)			PRIORITY	(Multiple Items)			PRIORITY	(Multiple Items)			PRIORITY	(Multiple Items)			PRIORITY	(Multiple Items)			PRIORITY	(Multiple Items)			PRIORITY	(Multiple Items)			PRIORITY	(Multiple Items)		PRIORITY	(Multiple Items)		PRIORITY	(Multiple Items)
3
4	PRIORITY	Sum of Plts			Row Labels	Sum of Plts			Row Labels	Sum of Plts			Row Labels	Sum of Plts			Row Labels	Sum of Plts			Row Labels	Sum of Plts			Row Labels	Sum of Plts			Row Labels	Sum of Plts		Row Labels	Sum of Plts		Row Labels	Sum of Plts
5	Truro	6			Inverness	6			Ilkeston	5			Cheltenham	5			Cannock	2			Bromborough	4			Redditch	4			Grand Total			Grand Total			Grand Total
6	Plymouth	10			Perth	5			Burton	6			Gloucester	8			Stafford	5			St Helens	10			Walsall	4
7	Torquay	6			Livingston	5			Barrow In Furness	5			Lincoln	7			Crewe	7			Liverpool	6			Telford	10
8	Exeter	7			Falkirk	6			Blackburn	5			Chester	6			Fenton	4			Trafford	13			Shrewsbury	7
9	Barnstaple	6			Clydebank New	4			Bristol Cribbs Causeway	6			Altrincham	4			Newcastle Under Lyme	8			Stockport	9			Grand Total	25
10	Taunton	7			Kilmarnock	6			Hereford	8			Kidderminster	4			Chesterfield New	4			Redditch	3
11	Yeovil	3			Uddingston	8			Worcester	9			Wolverhampton	6			Bangor	7			Grand Total	45
12	Grand Total	45			Mansfield	5			Cheltenham	1			Cannock	5			Bromborough	8
13					Grand Total	45			Grand Total	45			Grand Total	45			Grand Total	45
14
15
16
17
18
19
20
21
Trunks

Cell Formulas
Range	Formula
C1	C1	=IF($B$1="Load 1",('Timings Stk1'!$F$4),(""))
G1	G1	=IF($F$1="Load 2",('Timings Stk1'!$F$5),(""))
K1	K1	=IF($J$1="Load 3",('Timings Stk1'!$F$6),(""))
O1	O1	=IF($N$1="Load 4",('Timings Stk1'!$F$7),(""))
S1	S1	=IF($R$1="Load 5",('Timings Stk1'!$F$8),(""))
W1	W1	=IF($V$1="Load 6",('Timings Stk1'!$F$9),(""))
AA1	AA1	=IF($Z$1="Load 7",('Timings Stk1'!$F$10),(""))
AE1	AE1	=IF($Z$1="Load 8",('Timings Stk1'!$F$11),(""))
AH1	AH1	=IF($Z$1="Load 9",('Timings Stk1'!$F$12),(""))
AK1	AK1	=IF($Z$1="Load 10",('Timings Stk1'!$F$13),(""))
AM1	AM1	=SUM(IF(FREQUENCY(IF(LEN('Result 1'!D3:D1006)>0,MATCH('Result 1'!D3:D1006,'Result 1'!D3:D1006,0),""),IF(LEN('Result 1'!D3:D1006)>0,MATCH('Result 1'!D3:D1006,'Result 1'!D3:D1006,0),""))>0,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

My data is above and the copy scenarios are here:

NEW WAVE FILE TRUNKS.xlsm
	J	K	L
1	Loads	Cells to copy
2	1	A1	B20
3	2	A1	F20
4	3	A1	J20
5	4	A1	N20
6	5	A1	R20
7	6	A1	V20
8	7	A1	Z20
9	8	A1	AD20
10	9	A1	AH20
11	10	A1	AL20
Timings Stk1

I would normally try and record a macro but have no idea how to select cells based on a condition.
Any support would be greatly appreciated.
Thanks for taking the time
Sara

 

[tonyyy]

Hey Sara,
You might consider the following...

Sub ConditionalCopy()
Dim Col As Long
For Col = 2 To 36 Step 4
    If ActiveSheet.Cells(1, Col) <> "" Then Cells(20, Col) = Range("A1")
Next Col
End Sub

The code examines cells B1, F1, J1 through AJ1, and if not blank, fills the corresponding cell in row 20 with A1.
Cheers,
Tony