VBA to copy data from source to destination with unique column match

[Namita]

Hi All,
I need to copy the data with range I:II ('Comments 1' column to 'Comments 235' col ) from Trial_phase_1 to Trial_phase_1 when patient ID, Visit and Visit Date matches between two sheets.
Some of the text entered in color coded which need to be reflected exactly after copying to next sheet.




Is it possible that a pop up will appear (InputBox function) which will ask 'enter name of source sheet' and once entered, it will again ask for 'enter name of copied sheet' and after that the program should run.

Also note that the column names are different in my project (not like commet1 to comment235).
Thank you
Namita

[arnelgp]

can you use =Vlookup() function?

[Namita]

Thank you sir for the suggestion.
As there are more than 150 columns, we need to insert formula individually for every columns.
Along with, the vlookup function will stay in each cell which is again a double task to deactivate it.
Is it possible to develop one macro which can do all the activities at a time.

[arnelgp]

on the Ribbon->Home->Show Userform (right most) (click this menu).

[Namita]

Hello sir, when I clicked on 'show userform', I can't find any pop-up or panel to select source and copied sheet.
I am newbie. can you please check once

[arnelgp]

go to the folder where you download the xlsm.
right click on xlms file to show the Property, and Tick Unblock:
30589
open the xlsm file and Enable all macros.

[Namita]

Thank you so much sir. Its working perfectly.
However, I am not aware of userform and therefore, when the program run, it should ask to enter source and dest sheet name and then update the changes. I referred your code and modified like below. I am getting some error. May I request you to kindly help one more time.



Sub CopyComment()


Dim n As Long, m As Long, z As Long, x As Long, y As Long
Dim shet1 As String
Dim shet2 As String
shet1 = InputBox("Enter the name of the source sheet.")
shet2 = InputBox("Enter the name of the destination sheet.")

With shet2


For n = x To y
For m = a To b
If shet1.Range("A" & m) & shet1.Range("C" & m) = .Range("A" & n) & .Range("C" & n) Then
shet1.Range("I" & m & ":II" & m).Copy _
shet2.Range("I" & n & ":II" & n)
Exit For
End If
Next m
Next n
End With
End Sub

[Aussiebear]

How much often would it be likely to occur that the target sheet and the destination sheet not be as you indicated in your initial post? As I read it, you know both the target sheet and the destination sheet so asking for sheetnames is somewhat irrelevant.

[arnelgp]

you test this one.

[Namita]

Thank you sir once again. It is working fine now