VBA to copy column based on a header matching a cell value (date). The paste formulas into another column. Finally, hard code values after copying.

[vekdas]

I am not great with VBA and I am trying to automate a forecasting spreadsheet. Any help would be much appreciated.

In reality the spreadsheet is more complicated but in simplified terms:

There are 3 sheets used to forecast Market Shares:
Sheet 1: Lookup sheet which shows the latest month market share for all the markets. Another column shows the lates month of the market share (same for all markets - this is duplicated because this is how the data is extracted):

Sheet 2 - Completed by Territory Managers for forecasting:
The column for the data's latest month will have formulas looking up the market share in each market in rows 4,6,8,10... and calculating product sales in rows 5,7,9,11... based on multiplying the market share in the cell above the market sales in column B.
The territory manager will then add their forecast market shares for each market for all months after the latest month (non green headers)
All historical months data need to be hard coded to remove formulas and only show values.

Sheet 3 - same as sheet 2 but for a different territory

So, I need a VBA to:
1. On Sheet 2, find the column whose header (on row 3) matches the Latest Month in Column B of Sheet 1 (the lookup sheet). In the example, that would be column L (October 2021)
2. Copy all the cells below the header in that column. The formulas (not values) from this column should be copied and pasted into the column to the right. In the example, that would be column M (November 2021). The formula needs to be copied in a way that it will be updated to lookup the new header.
3. Copy the same column identified in step 1 again and paste as values over the top of itself to hard code the data and remove all formulas.
4. Repeat this will Sheet 3 (territory 2). In reality this would need to be repeated for several territories.

Thank you for any help!!

Mini Sheet:

TEST.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Actual m/s
2
3	MARKET	MARKET SALES	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22
4	MARKET 1	£87,661	7.7%	8.7%	9.7%	10.7%	11.7%	12.7%	13.7%	14.7%	15.7%	16.7%	5.0%	5.0%	10.0%	20.0%	25.0%	25.0%	30.0%
5	MARKET 1	£87,661	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 1,217	£ 365	£ 365	£ 731	£ 1,461	£ 1,826	£ 1,826	£ 2,192
6	MARKET 2	£122,763	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	5.0%	7.0%
7	MARKET 2	£122,763	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 512	£ 716
8	MARKET 3	£208,414	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	2.0%	5.0%	10.0%
9	MARKET 3	£208,414	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 347	£ 868	£ 1,737
10	MARKET 4	£141,550	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.2%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
11	MARKET 4	£141,550	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 25	£ -	£ -	£ -	£ -	£ -	£ -	£ -
12	MARKET 5	£151,624	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	5.0%
13	MARKET 5	£151,624	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 632
14	MARKET 6	£126,254	0.0%	0.0%	0.0%	0.0%	0.0%	0.2%	1.2%	2.2%	3.2%	4.2%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
15	MARKET 6	£126,254	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 441	£ -	£ -	£ -	£ -	£ -	£ -	£ -
16	MARKET 7	£116,837	43.4%	44.4%	45.4%	46.4%	47.4%	48.4%	49.4%	50.4%	51.4%	52.4%	3.0%	3.0%	3.0%	3.0%	3.0%	3.0%	3.0%
17	MARKET 7	£116,837	£ 292	£ 97	£ 97	£ 97	£ 97	£ 97	£ 195	£ 97	£ 195	£ 5,105	£ 292	£ 292	£ 292	£ 292	£ 292	£ 292	£ 292
18	MARKET 8	£76,413	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.9%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
19	MARKET 8	£76,413	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 60	£ -	£ -	£ -	£ -	£ -	£ -	£ -
20	MARKET 9	£42,440	0.0%	0.0%	0.0%	0.0%	0.4%	1.4%	2.4%	3.4%	4.4%	5.4%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
21	MARKET 9	£42,440	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 190	£ -	£ -	£ -	£ -	£ -	£ -	£ -
22	MARKET 10	£113,677	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.6%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
23	MARKET 10	£113,677	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 57	£ -	£ -	£ -	£ -	£ -	£ -	£ -
24	MARKET 11	£68,166	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	1.0%	2.0%	3.0%	0.0%	0.0%	0.0%	0.0%	2.0%	4.0%	6.0%
25	MARKET 11	£68,166	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 170	£ -	£ -	£ -	£ -	£ 114	£ 227	£ 341
26	MARKET 12	£23,578	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.1%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
27	MARKET 12	£23,578	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 2	£ -	£ -	£ -	£ -	£ -	£ -	£ -
28	MARKET 13	£234,504	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.3%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
29	MARKET 13	£234,504	£ 391	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ -	£ 53	£ -	£ -	£ -	£ -	£ -	£ -	£ -
Territory 1

Cell Formulas
Range	Formula
A5,A29,A27,A25,A23,A21,A19,A17,A15,A13,A11,A9,A7	A5	=A4
L29:S29,L5:S5,L27:S27,L25:S25,L23:S23,L21:S21,L19:S19,L17:S17,L15:S15,L13:S13,L11:S11,L9:S9,L7:S7	M5	=($B5/12)*M4
L4,L6,L8,L10,L12,L14,L16,L18,L20,L22,L24,L26,L28	L4	=XLOOKUP(A4,'Latest MS'!A:A,'Latest MS'!C:C)

Named Ranges
Name	Refers To	Cells
'Latest MS'!_FilterDatabase	='Latest MS'!$A$1:$C$43	L4, L6, L8, L10, L12, L14, L16, L18, L20, L22, L24, L26, L28

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A121,C3:Z3	Expression	=A3<='Latest MS'!$B$2	text	NO

 

[vekdas]

The following VBA finds Column L on Sheet 2 that is linked to the latest month on Sheet 1 (October 2021).
The code below then copies the formulas in that column and pastes them into the next column as required.

The problem is that it is also copying the header. I just want it to copy the cells beneath the header in that column.

I also need to copy the column again and paste as values over itself after copying the formulas to the adjacent column.

Any help would be much appreciated!


Sub Demo()
Dim vDate As Variant
Dim lr As Long
Dim Col As Variant
Dim NextCol As Long


Set vDate = Sheets("Latest MS").Range("B2")

With ThisWorkbook.Worksheets("Territory 1")

Col = Application.Match(vDate, .Rows(3), 0)

NextCol = Col + 1

lr = .Cells(.Rows.Count, 1).End(xlUp).Row

.Cells(1, Col).Resize(lr, 1).Copy Destination:=Cells(1, NextCol)


End With
End Sub

 

[vekdas]

So I have figured out how to paste as values over the original column using the code below.

The only thing I cannot figure out is how to not copy the header across to the adjacent column in the first copy and paste section of the code?

Sub Demo()
Dim vDate As Variant
Dim lr As Long
Dim Col As Variant
Dim LastCol As Long

' Sets the vDate
Set vDate = Sheets("Latest MS").Range("B2")

With ThisWorkbook.Worksheets("Territory 1")

Col = Application.Match(vDate, .Rows(3), 0)

LastCol = Col + 1

'Copy and paste formulas to adjacent column

lr = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(1, Col).Resize(lr, 1).Copy Destination:=Cells(1, LastCol)


'Copy and paste values over the top of column

lr = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(1, Col).Resize(lr, 1).Copy
Cells(1, Col).PasteSpecial Paste:=xlPasteValues

End With

End Sub