Compare/Copy columns from two books?

[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!

 

[STEVE BELL]

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

 

[dailo]

Your right, doesn't look like it likes the set wkbk1 declaration.

 

[STEVE BELL]

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)............

 

[dailo]

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.

 

[STEVE BELL]

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

 

[dailo]

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" & lrw).FormulaR1C1
"=abs((RC[-2]-RC[-3])/RC[-2])"

Thanks

 

[STEVE BELL]

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