Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help appreciated
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help appreciated
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any Help Appreciated | Excel Worksheet Functions | |||
Your Help is Appreciated | Charts and Charting in Excel | |||
Help is much appreciated for- | Excel Discussion (Misc queries) | |||
Help would be much appreciated! | Excel Worksheet Functions | |||
Hello, again Help is appreciated | Excel Worksheet Functions |