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

Gkr1981

New Member
Joined
Feb 9, 2010
Messages
46
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)

VBA Code:
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

ABC
1LineNew ValueNew Value 2
211234ABC
3212345ZXC

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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Gkr1981

New Member
Joined
Feb 9, 2010
Messages
46
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

ABCDE
1PrimaryKeyLineNew Value 1New Value 2UpdateValue
2XY.11ABCD12341
3XY.22ZXCV98761
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,875
Messages
5,544,808
Members
410,635
Latest member
phoenix7771
Top