Compare/Copy columns from two books?

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
--
steveB

Remove "AYN" from email to respond
"dailo" <dailo.1sl4qe_1122059152.361@excelforum-nospam.com> wrote in message
news:dailo.1sl4qe_1122059152.361@excelforum-nospam.com...
>
> 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!
>
>
> --
> dailo
> ------------------------------------------------------------------------
> dailo's Profile:
> http://www.excelforum.com/member.php...o&userid=25500
> View this thread: http://www.excelforum.com/showthread...hreadid=389444
>

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

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


--
steveB

Remove "AYN" from email to respond
"dailo" <dailo.1slaaf_1122066350.4534@excelforum-nospam.com> wrote in
message news:dailo.1slaaf_1122066350.4534@excelforum-nospam.com...
>
> Your right, doesn't look like it likes the set wkbk1 declaration.
>
>
> --
> dailo
> ------------------------------------------------------------------------
> dailo's Profile:
> http://www.excelforum.com/member.php...o&userid=25500
> View this thread: http://www.excelforum.com/showthread...hreadid=389444
>

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


--
steveB

Remove "AYN" from email to respond
"dailo" <dailo.1slfuq_1122073565.4925@excelforum-nospam.com> wrote in
message news:dailo.1slfuq_1122073565.4925@excelforum-nospam.com...
>
> 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.
>
>
> --
> dailo
> ------------------------------------------------------------------------
> dailo's Profile:
> http://www.excelforum.com/member.php...o&userid=25500
> View this thread: http://www.excelforum.com/showthread...hreadid=389444
>

So I'm tweaking around a little more and I want the delta to display as a percentage how can I format this line so that it will display as a 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

--
steveB

Remove "AYN" from email to respond
"dailo" <dailo.1sqlz7_1122314801.7981@excelforum-nospam.com> wrote in
message news:dailo.1sqlz7_1122314801.7981@excelforum-nospam.com...
>
> So I'm tweaking around a little more and I want the delta to display as
> a percentage how can I format this line so that it will display as a
> percentage instead of a decimal?
>
> wkbk3.Sheets(x).Range("D1:D" & lrw).FormulaR1C1 =
> "=abs((RC[-2]-RC[-3])/RC[-2])"
>
> Thanks.
>
>
> --
> dailo
> ------------------------------------------------------------------------
> dailo's Profile:
> http://www.excelforum.com/member.php...o&userid=25500
> View this thread: http://www.excelforum.com/showthread...hreadid=389444
>