Need a small change in this macro!!

[gameover]

Hi,
I have got the following macro which compare column 1 of 2 excel sheets and then copy columns 2 and 3 for the common values present in both sheets. What I should I change in this macro so that I am able to copy more then 2 columns?

Sub test12()
Dim fn As String
fn = "'D:\Documents and Settings\rohit.malik\Desktop\Personal\Copy Macro\[book2.xls]sheet1'!"
With Workbooks("book1.xls").Sheets("sheet1")
    With .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Offset(, 1).Resize(, 2)
        .Formula = "=vlookup($a1," & fn & "$a:$d,column(b1),false)"
        .Value = .Value
    End With
End With
End Sub

Please reply at your earliest!!!

Thanks in advance to all of you!!!

 

[Makrini]

I can't see how your Macro would do as you describe...

To me it looks like it does this...

1. iterates through all the values in A1 - putting a vlookup in the corresponding "B" column - with the value of the "B" column in the other workbook/sheet


i.e copies only 1 column....

To copy more than 1 you would need to have a completely different solution - better to use a find-copy-paste solution

 

[Legacy 68668]

More columns ?
You have only one more column, since your loop up range is a:d

.Resize(, 2)

Change 2 to 3

If you need more columns, you will also need to expand $a:$d

 

[gameover]

Hi Makrini,

Its working fine and its copying 2 coulmns from sheet 2 to sheet 1! I am using it for a long time but cant crack it for more columns.

DO you have any other soultion because I have got over 10000 rows to search and copy from!!

Thanks again for your help!!!

 

[gameover]

Thanks Seiya,

Its perfectly working fine now!!! Thanks a lot again for your help!!

 

[Makrini]

my apologies - I didn't grasp the resize.

seiya has the right of it