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

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" wrote in message
...

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.


--
dailo
------------------------------------------------------------------------
dailo's Profile: http://www.excelforum.com/member.php...o&userid=25500
View this thread: http://www.excelforum.com/showthread...hreadid=389444

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" wrote in
message ...

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.


--
dailo
------------------------------------------------------------------------
dailo's Profile: http://www.excelforum.com/member.php...o&userid=25500
View this thread: http://www.excelforum.com/showthread...hreadid=389444

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" wrote in
message ...

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

--
dail
-----------------------------------------------------------------------
dailo's Profile: http://www.excelforum.com/member.php...fo&userid=2550
View this thread: http://www.excelforum.com/showthread.php?threadid=38944

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" wrote in
message ...

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