VBA Question (Automate Ctrl + F, Ctrl + Copy, Ctrl + Paste)

[FirmanHassan]

Hi All

I have a workbook, and I have included comments throughout the workbook symbolised with "xxxFH: comment", in which the comment varies depending on what I'm trying to comment on in that specific row. I've got a lot of them throughout the workbook.

I've basically included an additional sheet called "COMMENTS" and what I'd like to do is to build a Macro that would search all cells that contain the value (including but not exclusively) "xxxFH" and paste the cells into this sheet. There are a lot of xxxFH comments at the moment, so I would like to be able to paste all of these xxxFH comments in a column (consecutive row order) in the COMMENTS sheet. If possible, it would also be really great if instead of just pasting the values, I would be pasting the cell which links directly to the referenced xxxFH cell, so I can CTR: + [ to the relevant xxxFH cell in the relevant worksheet.

I've added a mini sheet to show what exactly I'm looking for.

Comment.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
1
2		Comments
3		Paste below
4		xxxFH: please delete	My idea is to be able to find xxxFH comments throughout the workbook, and paste it like this, not just cell values, but also referencing/linking the cell on where it is currently located.
5		xxxFH: replicate	I have kept these comments in the same sheet at the moment only for example purposes, but in a realistic environment, these comments would be in another sheet
6		xxxFH: check
7
8
9
10
11								xxxFH: please delete
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26																		xxxFH: replicate
27
28
29
30
31								xxxFH: review
32
33
34
35
36
37
38
39													xxxFH: check
40
41
42
43
44
45								xxxFH: workin in progress
46
47
48
49											xxxFH: please delete
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64																					xxxFH: replicate
65
66
67
68
69											xxxFH: review
70
71
72
73
74
75
76
77																xxxFH: check
78
79
80
81
82
83
COMMENTS

Cell Formulas
Range	Formula
B4	B4	=COMMENTS!H11
B5	B5	=COMMENTS!R26
B6	B6	=COMMENTS!P77

Any help would be really great!