none
VBA Copying Data from one Sheet to another RRS feed

  • Question

  • Every week I receive a report with current sales figures. I then have to manually compile them into a separate workbook sheet that has all sales. I am completely new to VBA. I was able to find some tutorials that helped me write code to transfer information from one sheet to the other automatically but was unable to implement it for more than one row. 

    The Weekly workbook (sheet name = MTD Raw Data) comes in with the following headers:

    Territory(a) GEO(b) Country(c) CountryName(d) ProdLine(e) CustNo(f) CustName(g) InvDate(h) InvNumber(i) ItemCode(j) ItemDescription(k) Qty Ship(l) Sales(m)

    The Compiled workbook (sheet name = Raw Data) has the following headers:

    Territory(a) GEO(b) Country(c) ProdLine(e) CustNo(f) CustName(g) InvDate(h) InvNumber(i) ItemCode(j) ItemDescription(k) Qty Ship(l)  Unit Price (m) Sales(n)

    Basically I need it to loop through the existing rows in the weekly sheet and copy them over to the compiled sheet. It needs to be added after the last set of data. Additionally, the reports are usually monthly, so if there was anyway to only add new data that would be ideal. 














    Tuesday, July 2, 2013 9:20 PM

All replies

  • We need a better explanation - are you compiling data from multiple workbooks? How do you tell if the data is new? Will old data ever appear in the weekly sheet after it has been removed from the compiled sheet?
    Wednesday, July 3, 2013 7:57 PM
  • Sorry about that. Here is a little more clarification. 

    I receive the data in a new workbook each week and it must be compiled into a sales workbook. 

    The weekly sheets have data from the entire month. So the first Friday I will receive a report with the weeks invoices. The next Friday I receive a report with the previous two weeks invoices. 

    I have no use for the weekly reports. I just want to have the new sales data to compile automatically when I receive the new sheet. 

    Monday, July 8, 2013 4:20 PM
  • Record a macro where you open the summary workbook and select the sheet with the data, and the column with the dates.  Finally, while recording the macro, open the weekly workbook and select the sheet with the data, and the column with the dates. Then post your code here.
    Monday, July 8, 2013 5:11 PM