VBA to find column heading and copy data

[karldugan]

Hi all,

I need some help with VBA code that finds the date and then copies the data from row 6 underneath it (the orange cells). The number of rows underneath 6 will be variable but these cells will always contain a forumla or hard code figure, there will always be a break line at the bottom of the data, I was going to use the following code, I just can't figure out how to get it to look at the date at the top and then start from the 6th row down.

The date it needs to find will come from a sheet called P&L and will always be in cell G5 in that sheet.

Range("H6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

FY21 Template.xlsm
	H	I
1
2
3
4	Oct-21	Nov-21
5	Forecast	Forecast
6	£95,000	£0
7	£0	£0
8	£0	£0
9	£0	£0
10	£0	£0
11	£0	£0
12	£0	£0
13	£0	£0
14	£0	£0
15	£0	£0
16	£0	£0
17	£0	£0
18	£0	£0
19	£0	£0
20	£0	£0
21	£0	£0
22	£0	£0
23	£0	£0
24	£0	£0
25	£0	£0
26	£0	£0
27	£0	£0
28	£0	£0
29	£0	£0
30	£0	£0
31	£0	£0
32	£0	£0
33	£0	£0
34	£0	£0
35	£0	£0
36	£0	£0
37	£0	£0
38	£0	£0
39	£0	£0
40	£0	£0
41	£0	£0
42	£0	£0
43	£0	£0
44	£0	£0
45	£0	£0
46	£0	£0
47	£0	£0
48
Forecast

Cell Formulas
Range	Formula
I4	I4	=EOMONTH(H4,0)+1
I6,H7:I47	I6	=Budget!H6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H36:H46	Expression	=IF(H$5="ACTUAL",TRUE,)	text	NO
H6:H35,H47	Expression	=IF(H$5="ACTUAL",TRUE,)	text	NO
G36:G46,I36:S46	Expression	=IF(G$5="ACTUAL",TRUE,)	text	NO
G47,G6:G35,I6:S35,I47:S47	Expression	=IF(G$5="ACTUAL",TRUE,)	text	NO

 

[Sektor]

If I correctly understood you, you just need to find cell with date. You can use Find method of Range object:

Sub CopyCells()
    Dim rw
    rw = Rows(4).Find(Sheets("P&L").[G5]).Row
    Range(Cells(rw, "H"), Cells(Rows.Count, "H").End(xlUp)).Copy
    '// Do something...
    '// ...
    
    '// Clear clipboard
    Application.CutCopyMode = False
End Sub

 

[karldugan]

If I correctly understood you, you just need to find cell with date. You can use Find method of Range object:

Sub CopyCells()
    Dim rw
    rw = Rows(4).Find(Sheets("P&L").[G5]).Row
    Range(Cells(rw, "H"), Cells(Rows.Count, "H").End(xlUp)).Copy
    '// Do something...
    '// ...
   
    '// Clear clipboard
    Application.CutCopyMode = False
End Sub

That always selects column H though, what I need it to do is find the column with that date in it, so if the date in P&L G5 = 01/12/21, it knows to go across to column J and then copy everything from row 6 down.

FY21 Template.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S
4	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22
5	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast
6	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
7	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
8	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
Forecast

Cell Formulas
Range	Formula
H6:S8	H6	=Budget!G6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H6	Expression	=IF(H$5="ACTUAL",TRUE,)	text	NO
H6:H35,H47	Expression	=IF(H$5="ACTUAL",TRUE,)	text	NO
G47,G6:G35,I6:S35,I47:S47	Expression	=IF(G$5="ACTUAL",TRUE,)	text	NO

 

[Sektor]

Beg your pardon:

Sub CopyCells()
    Dim col&
    col = Rows(4).Find(Sheets("P&L").[G5]).Column
    Range(Cells(6, col), Cells(Rows.Count, col).End(xlUp)).Copy
    '// Do something...
    '// ...
   
    '// Clear clipboard
    Application.CutCopyMode = False
End Sub

 

[karldugan]

Apologies Sektor - When I ran it the first time it just kept selecting column H, I've since gone back and this works. God knows what I was doing!

Thank you!

 

[Sektor]

You're welcome!