copy data from one workbook to another

**stoey** · 04-30-2009, 11:52 PM

hi,

can anyone help me how to create a macro in copying data from one workbook to another? I know how to link the workbook so that it can update the other workbook but I would like to know if it is possible to simply automatically copy one work book to another...I attached a sample workbooks. Book1 contains the data to be copied on Book2. please help me in copying the start and end time on book 1 going to book 2 once book2 is opened (assuming that book1 is opened as well). Appreciate any response..

Regards,

Stoey

**Leith Ross** · 05-01-2009, 12:46 AM

Hello stoey,

Place this macro in a VBA module in "Book2.xls". There is no error checking to see if "Book1.xls" is open. Change the workbook and worksheet names accordingly.

Sub CopyTimes()

  Dim SrcRng As Range
  Dim DstRng As Range
  
    Set DstRng = ThisWorkbook.Worksheets("Day3").Range("L10:K33")
    Set SrcRng = Workbooks("Book1.xls").Worksheets("Sheet1").Range("L10:K33")
    
      DstRng.Value = SrcRng.Value
      
End Sub

**stoey** · 05-01-2009, 02:46 AM

hi Leith,

I tried the code and it works when 2 workbooks are opened. I tried adding another worksheet on book1 and I made another workbook named book3 and placed the code below on book2.

Private Sub Workbook_Open()
  Dim SrcRng, Book3 As Range
  Dim DstRng As Range
  
  
  
    Set DstRng = ThisWorkbook.Worksheets("Day3").Range("L10:K33,O10:P33")

    Set SrcRng = Workbooks("Book1.xls").Worksheets("Sheet1").Range("L10:K33")
    Set SrcRng = Workbooks("Book1.xls").Worksheets("Sheet2").Range("L10:K33")
    
    Set DstRng = ThisWorkbook.Worksheets("Day4").Range("L10:K33")
    Set Book3 = Workbooks("Book3.xls").Worksheets("Sheet1").Range("L10:K33")
    
      DstRng.Value = SrcRng.Value
      DstRng.Value = Book3.Value
      
End Sub

however when i tried opening book 2 (assuming book 1 and book 3 are opened) the data from book 3 are the only ones that were copied. the data on book 1 were not... any ideas why this happened? I attached sample files again...

Regards,

Stoey

**stoey** · 05-01-2009, 10:49 AM

hi Leith,

any idea why the code i tried only copies book 3 data?

regards,

stoey

**Leith Ross** · 05-01-2009, 12:42 PM

Hello stoey,

I provided you a solution for 2 workbooks. Now, you present a problem with 3 workbooks. Explain to me what it is you want to do and I can provide you with the correct macro.

**stoey** · 05-01-2009, 01:35 PM

hi again Leith,

I apprecaite and thank you for the correct macro you provided earlier...actually what I am currently doing is I have 20 workbooks which I would like some specific data to be copied on one workbook. I attached sample workbooks before which is book1 and book2. Since you provided me a working code, I tried tweaking it and trying to learn about it so that I could apply it on the current program I am working on...

regards,

stoey