VBA script needed to select cells in an active row, copy and paste values only into other cells in the same row.

Question

I am using Office 365 on a Windows PC. I have an Excel workbook with two tabs - Call Log and Member List. There are at least three common columns on each tab - Member Name, ID # and DOB. ID #'s are unique.

When I enter an ID # in the Call Log column C there is an Index/Match formula that looks up the Member Name and DOB. This info appears in the green columns.

With four helper columns I can also find the ID # if the Member Name and DOB are provided. This info appears in the purple columns.

The workbook is Macro enabled. In the Call Log $H$1 is labeled ColNo and $I$1 is labeled RowNo. These cells display the active row and column numbers.

---

What I would like to do is create a macro for a button. When the button is selected the macro will do the following steps:

1. Read the number in the RowNo cell (which will be the active row). This may be an unnecessary step.
2. Select the cells in columns J, K and L in the active row.
3. Copy the data.
4. Go to column B in the active row.
5. Paste Special, Values Only. This will overwrite the data in columns B, C and D in the active row.

I can't make it work with the Record Macro function. All help will be greatly appreciated.

Answer 1

Could you share a test workbook? Then I will have a try on it.

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Answer 2

Sub Test()
Intersect(Range("B:D"), ActiveCell.EntireRow).Value = Intersect(Range("J:L"), ActiveCell.EntireRow).Value
End Sub

Answer 3

https://drive.google.com/drive/folders/1dIrBHNB4YhDyWYlgchiUm6k2EoL505TK?usp=sharing

Answer 4

Here's the macro code to copy data from the specified cells:

=======

Sub CopyData()

'Copy data from J to B 

Range("B" & ActiveCell.Row).Value = Range("J" & ActiveCell.Row).Value 

'Copy data from L to D 

Range("D" & ActiveCell.Row) = Range("L" & ActiveCell.Row).Value

'Copy data from K to C and upper cell C 

Range("C" & ActiveCell.Row).Value = UCase(Range("K" & ActiveCell.Row)) 

End Sub

=======