+ Reply to Thread
Results 1 to 5 of 5

Copy multiple cells from several work books to one workbook

  1. #1
    Registered User
    Join Date
    01-20-2008
    Posts
    3

    Copy multiple cells from several work books to one workbook

    Hello Everyone,
    I need your help desperately.....

    I am trying to take data from the same five cells from 145 Workbooks into one master Workbook. My wife created an Invoice for each customer using an excel template and labeled them Invoice 001 to 145.

    I want the cells "A16", "G15", "H36", "H37", "H38" from workbooks labeled "Invoice 001.xls" to "Invoice 145.xls" to feed column "B2", "C2", "D2", "E2", "F2" in Workbook "Income Statement Sheet.xls"

    Example:

    I want the cells "A16", "G15", "H36", "H37", "H38" from work book "Invoice 001.xls" to feed column "B2", "C2", "D2", "E2", "F2" in Work Book "Income Statement Sheet.xls"

    Then

    I want the cells "A16", "G15", "H36", "H37", "H38" from work book "Invoice 001.xls" to feed column "B3", "C3", "D3", "E3", "F3" in Work Book "Income Statement Sheet.xls"

    Then

    I want the cells "A16", "G15", "H36", "H37", "H38" from work book "Invoice 001.xls" to feed column "B4", "C4", "D4", "E4", "F4" in Work Book "Income Statement Sheet.xls"


    Etc Etc until data from all 145 Invoice Workbooks is in "Income Statement Sheet.xls" I tried doing a loop but I am afraid I am too stupid to get this done. I want to run the macro each time she creates a new Invoice for her customer. Any help would be greatly appreciated since we are trying to get ready for tax time.

    Thanks in advance.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524
    See if this thread can get you in the right direction
    http://www.excelforum.com/showthread.php?t=589590

  3. #3
    Registered User
    Join Date
    01-20-2008
    Posts
    3

    Smile Almost there?

    Great link I am almost there.....it runs fine but the final result is it only posts the data from the final invoice. I tested this macro with five invoices (142, 143, 144, 145, 146) Only the data from the 146 invoice workbook went onto the "Income Statement Sheet.xls".....something I did to kill the loop?


    Here is the VBA:

    Please Login or Register  to view this content.

    Thanks Dave....This websiet is Great!
    Last edited by VBA Noob; 01-20-2008 at 06:44 PM.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524
    Quote Originally Posted by Blur1973
    Great link I am almost there.....it runs fine but the final result is it only posts the data from the final invoice. I tested this macro with five invoices (142, 143, 144, 145, 146) Only the data from the 146 invoice workbook went onto the "Income Statement Sheet.xls".....something I did to kill the loop?


    Here is the VBA:

    Please Login or Register  to view this content.

    Thanks Dave....This websiet is Great!
    Please Login or Register  to view this content.
    you need to go farther down then xlup

  5. #5
    Registered User
    Join Date
    01-20-2008
    Posts
    3

    It is working

    Thanks Dave I am in working order. I needed to change the parts that are listed in red:

    Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("b2000").End(xlUp).Offset(1, 0) = Range("A16")

    Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("c2000").End(xlUp).Offset(1, 0) = Range("G15")

    Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("d2000").End(xlUp).Offset(1, 0) = Range("H36")

    Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("e2000").End(xlUp).Offset(1, 0) = Range("H37")

    Workbooks("Income Statement Sheet").Sheets("Master Sheet").Range("f2000").End(xlUp).Offset(1, 0) = Range("H38")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1