none
Macro to copy data

    Question

  • Hello,

    I am after developing a macro which will copy different pieces of information from another file. My issue is that these files are arranged in monthly folders, and new ones are being added all the time. It would be easy if the data came from the same file all the time but it doesn't. Every four days or so depending on production, a new excel file is added with a different name that the data has to come from.

    Can this be done? Or is it not possible because the file the information comes from isn't the same one every day?

    Thanks,

    Alex

    Friday, March 17, 2017 2:02 PM

All replies

  • re: "Can this be done? Or is it not possible because the file the information comes from isn't the same one every day?"

    Yes
    Friday, March 17, 2017 3:00 PM
  • How?
    Friday, March 17, 2017 3:22 PM
  • Hi Smithy2k7,

    Thanks for visiting our forum.

    Then this forum mainly discusses general questions and feedback for Microsoft Excel, since your query is more related to macro, I'll move your question to the following MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, March 20, 2017 5:10 AM
  • Hello,

    I am after a macro which is able to look in a number of different sheets of a file to pull out information and dump it onto a summary spreadsheet, which is a separate file. Each spreadsheet has four sheets with eleven pieces of information in each sheet.

    Another issue is that there are new files added a few times a week which I would also need the information taking from. These are arranged month by month in a folder for each month.

    So I basically need a macro which is A) capable of looking across multiple cells on four sheets and pulling the information back into my summary sheet B) able to identify when a new file has been added and look at this too C) Look in the monthly folders where these files are stored.

    Even though the information is spreadsheet out over multiple spreadsheets per month, the information required over the four sheets is always in the same cells.

    Can anyone help me please?

    Many thanks,

    Alex

    Monday, March 20, 2017 9:11 AM
  • Hello Alex,

    The code will depend on the data in the sheets. I'd suggest starting from the Getting Started with VBA in Excel 2010 article.


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Monday, March 20, 2017 1:01 PM
  • Hi smithy2k7,

    from the description of the thread , it looks like you want to copy data from different file to your current file.

    and every time there is a new file available from that you want to copy the data.

    so you can try to use open file dialog in your code.

    so that when you run the code , it will show you open file dialog and you can select  the desire file.

    so you can select any excel file , from where you can copy the data.

    refer example below.

    Sub demo()
    
    Dim sPath, FileSelected As String
    Dim sValue As String
    Dim wbTarget As Workbook
    Dim strName As String
    Dim myFile As Object
    strName = "Sheet1"
    Set myFile = Application.FileDialog(msoFileDialogOpen)
    With myFile
    .Title = "Choose File"
    .AllowMultiSelect = False
    .InitialFileName = "C:\Users\v-padee\Desktop\excel files"
    If .Show <> -1 Then
    Exit Sub
    End If
    FileSelected = .SelectedItems(1)
    End With
    
    
    Set wbTarget = Workbooks.Open(FileSelected)
    sValue = wbTarget.Sheets(1).Range("A1").Value
    
    ThisWorkbook.Sheets(1).Range("A1").Value = sValue
    ThisWorkbook.Save
    End Sub
    
     

    you can change the path of initial file name and set that folder path where you store all the files.

    in that folder you can sort the files, so when you open the file dialog you can see the new latest file.

    so you can easily find the file.

    you can modify the code as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 1:31 AM
    Moderator
  • If I were you I would record a Macro to do the work.  Then, look at the code and find the date part.  Put that in a variable, like maybe in a cell.  Something like =TODAY(), or =TODAY()+4.  Finally, reference the sheet and cell (range) in your Macro.  Run everything through the Task Manager, like in the example below.

    http://www.digitalcitizen.life/how-create-task-basic-task-wizard

    To run a script when Excel opens, just follow the steps in the link below.

    http://www.ozgrid.com/VBA/auto-run-macros.htm

    Done.


    MY BOOK

    Friday, March 24, 2017 2:29 AM