Sub CopyData()
Dim varr(1 to 2, 1 to 2) as String
Dim src(1 to 2) as String
Dim sh1 as Worksheet, sh2 as Worksheet
Dim srcSh as Worksheet
Dim bk1 as Workbook
Dim bk2 as Workbook
Dim r1 as variant
Dim r2 as variant
Dim s1 as variant
Dim s2 as variant
Dim i As Long, j As Long
s1 = Array("B9","C9","D9")
r1 = Array("O8","O10","O12")
s2 = Array("B10","C10")
r2 = Array("O5","O4")
Set bk1 = Workbooks("Book1.xls")
Set bk2 = Workbooks("Book2.xls")
varr(1,1) = "CA A"
varr(1,2) = "CA B"
varr(2,1) = "DG A"
varr(2,2) = "DG B"
src(1) = "CA"
src(2) = "DG"
for i = 1 to 2
set sh1 = bk2.Worksheets(varr(i,1))
set sh2 = bk2.Worksheets(varr(i,2))
set srcsh = bk1.Worksheets(src(i))
for j = lbound(s1) to ubound(s1)
sh1.Range(r1(j)).Value = srcSh.Range(s1(j)).Value
Next
for j = lbound(s2) to ubound(s2)
sh2.Range(r2(j)).Value = srcSh.Range(s2(j)).Value
Next
Next
End sub
Untested.
--
Regards,
Tom Ogilvy
Diane Mallin wrote in message
...
I have 2 workbooks and want to copy values from Book 1 to Book 2
Book 1 has 2 worksheets called CA and DG
Book 2 has 4 worksheets called CA A, CA B, DG A and DG B
Starting with sheet CA in book 1 I need to
Copy from Cell Paste to Sheet Cell
b9 CA A o10
c9 CA A o8
d9 CA A q8
b10 CA B o5
c10 CA B o4
This continues down a longer list always copying from CA to CA A or CA B.
Once the CA data has been copied I want to be able to repeat the same
proceedure substituting DG for CA
and DG A & DG B for CA A & CA B.
The cell references are always the same. (i.e. copy cell b9 from DG to
cell
o10 in DG A etc...)
Any help in achieving this using vb would be appreciated by a beginner
Regards
Gazza