Compare/Copy columns from two books?

  • Thread starter Thread starter dailo
  • Start date Start date
D

dailo

I've been searching throughout this forum and have found lots of
examples but none of them seem to do quite what I want.

I have two wookbooks which are formatted identical and I want to copy a
column from each workbook (numeral values) and then in a third column
display the difference between the values. Each workbook has 30 sheets
and I would like it to go through all 30 sheets copying a particular
column from each one and then comparing them and display the difference
into a new workbook that contains 30 sheets with each sheet containing
the values from both books and the difference. Thanks for your help!
 
Something like the following might work [untested]
The tricky part is assigning the workbook names.
(set "A" to the column you are working with, same with B & C)

Dim wkbk1 as workbook, wkbk2 as workbook, wkbk3 as workbook, x as long, lrw
as long

set wkbk1 = Workbooks("Workbook1")
set wkbk2 = Workbooks("Workbook2")
set wkbk3 = Workbooks("Workbook3")

For x = 1 to 30
lrw = wkbk1.Sheets(x).Cells(Rows.COUNT, "A").End(xlUp).Row
wkbk1.Sheets(x).Range("A1:A" & lrw).Copy _
Destination:=wkbk3.Sheets(x).Range("A1")

lrw = wkbk2.Sheets(x).Cells(Rows.COUNT, "A").End(xlUp).Row
wkbk2.Sheets(x).Range("A1:A" & lrw).Copy _
Destination:=wkbk3.Sheets(x).Range("B1")

wkbk3.Sheets(x).Range("C1:C" & lrw).FormulaR1c1="=RC[-2]-RC[-1]"
Next
 
I always have trouble setting workbooks.

did you change "Workbook1", "Workbook2", "Workbook3" to the
actual names of the 3 workbooks?

If yes, than try changing to strings

dim wkbk1 as String, wkbk2.....

wkbk1 = "My Workbook" <<< use actual name
wkbk2 =
wkbk3 =

Than use Workbooks(wkbk1).Sheets(x)........
Workbooks(wkbk2)............
Workbooks(wkbk3)............
 
Thanks it works! Didn't realize that the files had to saved onto the
disk, or at least that's what seemed to have fix it. Just need to
tweak it a little more to make it format better and easier to use.
Thanks.
 
Very happy to hear that it worked!

In your spare time - put the following into any macro and run it...
watch what happens...

Dim wkbk As Workbook
For Each wkbk In Application.Workbooks
MsgBox wkbk.Name
Next
 
So I'm tweaking around a little more and I want the delta to display a
a percentage how can I format this line so that it will display as
percentage instead of a decimal?

wkbk3.Sheets(x).Range("D1:D" & lrw).FormulaR1C1
"=abs((RC[-2]-RC[-3])/RC[-2])"

Thanks
 
with wkbk3.Sheets(x).Range("D1:D" & lrw)
.FormulaR1C1 ="=abs((RC[-2]-RC[-3])/RC[-2])"
.NumberFormat = "0.00%"
end with
 
Back
Top