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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,210
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

AVAWAXExample of a cell refso this would be the name to copy in BA1
13Header NameCell containing dataBA1TOM
14Name$BA$1the cell refs are for the Form1 sheet
15Add1$CC$2
16Add2$BH$7
17Tel$CF$8
18Date$BJ$9
19Sold$BB$4

Example of RAW sheet

ColumnHIJKLM
Row 10DateSoldNameAdd1Add2Tel
1/1/2023yesBillyhomeaway999
16/2/23noSuecottageessex911
so this would be the row i need all the data pasted intoplease paste values onlyTOM would go here!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi
If I did understand you
May be!!
VBA Code:
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
 
Upvote 0
Solution
Hi Mohadin,
this looks great, think it will do what i need thank you so much :)
Tony
 
Upvote 0
You are very welcome Tony
And thank you for the feedback
glad I could help
Be happy and safe
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top