How to send data from an opened workbook to another opened workbook?

[aba104]

Hi, everyone,

I need to open an empty workbook, and receive data from other six opened workbooks. Could anyone tell me how to achieve this?

Thanks a lot. I need this urgently.

JY

 

[VBAjedi]

Post more detail. Why do you think Excel's built-in linking functions will not work? Why does it have to be an empty workbook? Does it have to be a NEW empty workbook, or can it be one you set up with VBA? What's the purpose of the new book? Is your source data in exactly the same place in each of the six books, or is it in different places? Have you applied Named Ranges to your source data?

The quality of the answer you will receive depends on your ability to describe the details of your situation. Give us more to work with!

VBAjedi

 

[aba104]

Hi, VBAjedi

Sorry for not providing detail. The emptywork I meant is one I set up with VBA. The purpose of this book is to collect data from other 6 workbooks. My source data is in exactly the same place in each of the six books. I want to open this new workbook everyday, then open other 6 workbooks, and get data from these 6 books, then save this new book with a file name.

Your earliest reply will be appreciated.
Thank you.

JY

 

[VBAjedi]

So your VBA code actually resides in yet another workbook? Or one of the existing 6?

The following is a modification of a snippet Skip Vought posted in thread707-689753 . You would need to have your six source workbooks open (although you could write code to do that for you if they are always in the same directory). Also, you need to decide where you are going to write the data on the target sheet. You can either paste it to a predetermined place every time (as this example does), or you can add code to paste each chunk of data a few rows after the last chunk of data ends:

wkbThis = Workbooks.Add
wsThis = wkbThis.Worksheet("Sheet1")  ' the target sheet
for each wb in workbooks
  select case wb.name
  case "SourceBook1.xls".
    wb.range("A1:G20").Copy _
      Destination:=wsThis.Range("A2")
  case "SourceBook2.xls"
    wb.range("A1:G20").Copy _
      Destination:=wsThis.Range("A25")
  end select
  ' etc for the next four source workbooks
next

Hope that points you in the right direction. Let us know if you need additional assistance or if this doesn't work for you for some reason!



VBAjedi

 

[aba104]

VBAjedi,

Thank you so much for your help. With your help I finally solved my problem. Thanks and have a good week!

JY