VBA: Range on Columns and Rows

[1dwn5up]

Hello Everyone,

I'm very new to VBA/Macros and don't know how to get the required results. I've searched but not sure if it's been posted before. I'm only able to create the vba for one month, however as I have all 12 months of data in separate columns, I'm not sure how to get it to select the month based on a cell within the workbook.

Goal 1: To create a VBA macro to copy > Paste Special Values for the earliest forecast (in this example March) over to column P based on the Month in cell P6 or P7 (+1) to get to the next month (i.e. 2 or Feb + 1 = 3 or March).
Goal 2: I plan to have the same sheet multiple times, How would you be able to create a macro/vba for each tab to perform the same function of copying over March > column P for each tab so you execute it once for the entire workbook?

Thank you,
Harry

VBA Range Columns and Rows Example.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
1			2	Feburary																								Drop-down menu for cells C1 & D1
2
3																												1	January
4			Actual	Actual	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast		Forecast												2	Feburary
5																												3	March
6			1	2	3	4	5	6	7	8	9	10	11	12		2	Formula based on cell C1											4	April
7			January	Feburary	March	April	May	June	July	August	September	October	November	December		Feburary	Formula based on cell D1											5	May
8		Account 1	10	11	15	10	10	10	10	10	10	10	10	10		15												6	June
9		Account 2	20	21	25	10	10	10	10	10	10	10	10	10		25												7	July
10		Account 3	30	31	35	10	10	10	10	10	10	10	10	10		35												8	August
11		Account 4	40	41	45	10	10	10	10	10	10	10	10	10		45												9	September
12		Account 5	50	51	55	10	10	10	10	10	10	10	10	10		55												10	October
13		Account 6	60	61	65	10	10	10	10	10	10	10	10	10		65												11	November
14		Account 7	70	71	75	10	10	10	10	10	10	10	10	10		75												12	December
15		Account 8	80	81	85	10	10	10	10	10	10	10	10	10		85
16		Account 9	90	91	95	10	10	10	10	10	10	10	10	10		95
17		Account 10	100	101	105	10	10	10	10	10	10	10	10	10		105
18
19
20		Goal 1:	To create a VBA macro to copy > Paste Special Values for the earliest forecast (in this example March) over to column P based on the Month in cell P6 or P7 (+1) to get to the next month (i.e. 2 or Feb + 1 = 3 or March).
21			The March forecast values in column E will then be overwritten with actuals.
22			The following month, the process would be repeated, and the April forecast in column F would be copied over ot column P.
23
24
25		Goal 2:	There will be exact duplicates of this sheet (e.g. Sheet1) within the same workbook/file with different data.
26			How would you be able to create a macro/vba for each tab to perform the same function of copying over March > column P for each tab so you execute it once for the entire workbook?
Sheet1

Cell Formulas
Range	Formula
P6	P6	=C1
P7	P7	=D1

Cells with Data Validation
Cell	Allow	Criteria
C1	List	=MonthNum
D1	List	=Month

 

[kevin9999]

Hello Harry and welcome to the forum
Personally, I wouldn't use VBA when you could achieve the same result with some inbuilt 365 functions. Please consider the following alternative, using the Filter function. If all your sheets are the same layout/format then you can simply copy the first sheet - the formulas will refer to the same ranges in each sheet. I've assumed that when you select December that you may want January's forecast? Anyhow, please note the formulas in P6, P7 and P8.

Harry.xlsx
	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
1			2	Feburary
2
3																												1	January
4			Actual	Actual	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast		Forecast												2	Feburary
5																												3	March
6			1	2	3	4	5	6	7	8	9	10	11	12		3												4	April
7			January	Feburary	March	April	May	June	July	August	September	October	November	December		March												5	May
8		Account 1	10	11	15	10	10	10	10	10	10	10	10	10		15												6	June
9		Account 2	20	21	25	10	10	10	10	10	10	10	10	10		25												7	July
10		Account 3	30	31	35	10	10	10	10	10	10	10	10	10		35												8	August
11		Account 4	40	41	45	10	10	10	10	10	10	10	10	10		45												9	September
12		Account 5	50	51	55	10	10	10	10	10	10	10	10	10		55												10	October
13		Account 6	60	61	65	10	10	10	10	10	10	10	10	10		65												11	November
14		Account 7	70	71	75	10	10	10	10	10	10	10	10	10		75												12	December
15		Account 8	80	81	85	10	10	10	10	10	10	10	10	10		85
16		Account 9	90	91	95	10	10	10	10	10	10	10	10	10		95
17		Account 10	100	101	105	10	10	10	10	10	10	10	10	10		105
18
Sheet1

Cell Formulas
Range	Formula
P6	P6	=IF(C1=12,1,C1+1)
P7	P7	=IF(D1="December","January",OFFSET(INDEX($C$7:$N$7,MATCH($D$1,$C$7:$N$7,0)),0,1,1,1))
P8:P17	P8	=FILTER($C$8:$N$17,$C$7:$N$7=$P$7,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C1	List	=$AB$3:$AB$14
D1	List	=$AC$3:$AC$14