VBA to copy data in cells and find header to paste into

[tonywatsonhelp]

Hello Everyone,
I have two sheets
RAW
Form1

in Form1 I have a list of Names in AV14 to AV last row,
Next to this i have cell addresses like this "$BF$19" this is the cell the data for that name is in,
all cells or only every one cell.


now in RAW i have headers in row 10,
then data down,
best way to get Raws last row would be to use Column "A" for everything.

So what I want is a macro that will go down the names in Sheet "Form1" range"AV14:AV Lastrow" get the value in the cell shown in column AW for that data,
then goto RAW sheet, look along row 10 and find the header, then please then value in the last row to create a new line.

so basiicly, its copy and paste date from one sheet to another, but instead of knowing the cells you look them up and find then.

Heres an example of Sheet Form1

	AV	AW	AX		Example of a cell ref	so this would be the name to copy in BA1
13	Header Name	Cell containing data			BA1	TOM
14	Name	$BA$1	the cell refs are for the Form1 sheet
15	Add1	$CC$2
16	Add2	$BH$7
17	Tel	$CF$8
18	Date	$BJ$9
19	Sold	$BB$4

Example of RAW sheet

Column	H	I	J	K	L	M
Row 10	Date	Sold	Name	Add1	Add2	Tel
	1/1/2023	yes	Billy	home	away	999
	16/2/23	no	Sue	cottage	essex	911
so this would be the row i need all the data pasted into	please paste values only		TOM would go here!

 

[mohadin]

Hi
If I did understand you
May be!!

Sub test()
    Dim a
    Dim i&, r&
    With Sheets("Form1")
        a = Range(.Range("AV14"), .Range("AW14").End(xlDown))
    End With
    With Sheets("ROW")
        For i = 1 To UBound(a)
            r = .Cells.Find(a(i, 1), , , 1).Column
            .Cells(.Cells(Rows.Count, r).End(xlUp).Row + 1, r).Value = Sheets("Form1").Range(a(i, 2))
        Next
    End With
End Sub

 

[tonywatsonhelp]

Hi Mohadin,
this looks great, think it will do what i need thank you so much
Tony

 

[mohadin]

You are very welcome Tony
And thank you for the feedback
glad I could help
Be happy and safe