Help appreciated

  • Thread starter Thread starter Diane Mallin
  • Start date Start date
D

Diane Mallin

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
 
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.
 
Back
Top