Copying columns (but cells should be copied in different order)between two files or worksheets

[suresharaja]

Hello All,

I have two excel files. In both the excel files I have about 1300 rows
of data. These are different datas of the same material. For example
"material A" (the material information in both the files is in column
A) in file 1 is in row 13 and the same "material A" in file 2 is in
row 1201. I am trying to write a script which can take one file say
file 1 (for simplicity) and go to row 1 (of worksheet 1) and read the
material name (say "material xyz") and locate this material in file 2
(may be this same "material xyz" exist in row 1100 in file 2) and copy
a specific cell (say cell in column c, which means for this example,
cell c1100) to file1 in the same worksheet but in a specific column z
(so copy from file 2, cell c1100 TO file 1, cell z1). Can anyone help
me.

Thanks

Suresh

 

[Joel]

set bk1sht = workbooks("book1.xls").sheets("Sheet1")
set bk2sht = workbooks("book2.xls").sheets("Sheet1")

with bk1sht
RowCount = 1
do while .Range("A" & RowCount) <> ""
Material = .Range("A" & RowCount)

set c = bk2sht.Columns("A").find(what:=Material, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find : " & material)
else
.Range("Z" & Rowcount) = bk2sht.Range("C" & c.row)
end if
RowCount = RowCount + 1
loop
end with

 

[suresharaja]

Hello Joel,

Thank you very much for the reply. But a basic question (as I am a
total beginner), how to run this script. I tried with the inbuilt, VBE
but I dont know which button to press. Thank you

Suresh

 

[Joel]

fYou need to have a beginning and end line that I put into the code like I
did below

1) to get into VBA type Alt F11
2) In VBA menu Insert Module
3) Past code below
4) from VBA Window check code in menu Debug - compile VBA Project
5) You can run code a few difffernt ways
a) From VBA Window press F5
b) from VBA window menu Run - Run
c) from worksheet menu Tools - Macro - Macro - (macro Name) Run
d) from worksheet menu Tools - Macro - Macro. select Options and set a
hot key to run macro
e) Put a control button on worksheet/toolbar and use button to start
macro. Will explain if you need this option


There are also macro the are triggered fron event.


Sub Getmaterial

set bk1sht = workbooks("book1.xls").sheets("Sheet1")
set bk2sht = workbooks("book2.xls").sheets("Sheet1")

with bk1sht
RowCount = 1
do while .Range("A" & RowCount) <> ""
Material = .Range("A" & RowCount)

set c = bk2sht.Columns("A").find(what:=Material, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find : " & material)
else
.Range("Z" & Rowcount) = bk2sht.Range("C" & c.row)
end if
RowCount = RowCount + 1
loop
end with

end sub