Copy Cell value and paste in a new cell using VBA and Function

[Gkr1981]

Hi All,

I have done some digging on your site and i have found the below code, which i believe works 98% for me.

What I am doing is having a table i need to update a cell in based on a primary key match.

(i copied this code from a previous topic in this forum)

Sub findAndCopy()
  Dim foundCell As Range, sh1, sh2 As Worksheet
  
  'Set sheets
  Set sh1 = Sheets("Data Capture")
  Set sh2 = Sheets("DataTable")
  
  'Find string in column A of Sheet2
  Set foundCell = sh2.Range("Table5[PrimaryKey]").Find(sh1.Range("Z2").Value, , xlValues, xlWhole)
  If Not foundCell Is Nothing Then 'If match cell is found
    sh1.Range("B2").Copy
    foundCell.PasteSpecial xlPasteValues
    foundCell.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
  Else
    Call MsgBox("Not found the match cell!", vbExclamation, "Finding String")
  End If
End Sub

What i'm hoping to do, is the following in the below table of new values

	A	B	C
1	Line	New Value	New Value 2
2	1	1234	ABC
3	2	12345	ZXC

x = row number based on the match

1. Copy value from B2 in sheet 1,
2. Look for line in Table5 based on value listed in Z2
3. PasteValues in Column Kx (in Table5)
4. Copy Value in C2
5. Look for line in Table5 based on value listed in Z2
6. PasteValues in Column Ox (in Table5)
7. Put today() value in column Zx for each of the mentioned above lines matched

This is repeated for each row in the update table (this can be anywhere from 1 - 8)

What changes do i need to make (sorry im just a tad confused about the match section)

Cheers
Graeme

 

[Gkr1981]

Bump

 

[Gkr1981]

This might be clearer.


What im trying to do is build a copy and paste macro, using a loop, match and offset.

Basically in the Update sheet.

I have a table where a user will update values

	A	B	C	D	E
1	PrimaryKey	Line	New Value 1	New Value 2	UpdateValue
2	XY.1	1	ABCD	1234	1
3	XY.2	2	ZXCV	9876	1
4

What i need this macro to do is the following.

1. Search Sheet2(Table1[PrimaryKey]) and match based on cell A2 value
2. Copy value in C2 in Sheet 1(eg ABCD)
3. Paste.Values in Lx (where x =row id found in step 1)
4. Copy value in CD in Sheet 1(eg 1234)
5. Paste.Values in Ox (where x =row id found in step 1)
6. Paste today() in Wx
7. Loop for all cells where Column E = 1

Hopefully this is clear.

Cheers