+ Reply to Thread
Results 1 to 8 of 8

Need help! Macro to open sequentially named files, copy and paste data into a master file

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    13

    Need help! Macro to open sequentially named files, copy and paste data into a master file

    Hi All,

    I've found this site very useful in the past but have an issue I'm not certain how to address. Basically I have a folder which has sequentially named files: "XXXX01.xlsx, XXXX02.xslx, XXXX04.xlsx....". Each XXXX##.xlsx is structured identically and contains 24 hourly data points.

    I basically want to create a master file with all hours appended in one file. So essentially I need a macro that will open XXXX01.xlsx, copy a range of data, paste it to a new file, open XXXX02.xlsx, copy the same range of data and append it to the bottom of the pasted data from XXXX01.xlsx.

    Any help is greatly appreciated!

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Need help! Macro to open sequentially named files, copy and paste data into a master f

    copy a range of data?
    What range? Which column/s.
    I think my understanding is the files are open based on alpha sequence on the folder.

  3. #3
    Registered User
    Join Date
    06-05-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Need help! Macro to open sequentially named files, copy and paste data into a master f

    More specifically each of the files XXXX##.xlsx contains columns (Date, type, hour, price volume)
    I specifically want to copy Range A3:G28 from each of the XXXX##.xlsx and aggregate all of these files (about 30 of them) into a master file.

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Need help! Macro to open sequentially named files, copy and paste data into a master f

    How many sheets are in each workbook and if there are more than one sheet, how many sheets within a book do you want to copy? If there is a single sheet, do not worry.

  5. #5
    Registered User
    Join Date
    06-05-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Need help! Macro to open sequentially named files, copy and paste data into a master f

    only a single sheet in each workbook

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,489

    Re: Need help! Macro to open sequentially named files, copy and paste data into a master f

    Here's a free add-in that will prompt you for specifics (path, file name pattern, etc.) and then merge the files.
    RDBMerge, Excel Merge Add-in for Excel for Windows

    Here's various code examples to merge files if you want to create your own.
    Copy/Paste/Merge examples

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Need help! Macro to open sequentially named files, copy and paste data into a master f

    Change this line

    Please Login or Register  to view this content.
    I put a header on the master sheet, please change the header too.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-05-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Need help! Macro to open sequentially named files, copy and paste data into a master f

    Wow thanks for the quick replies and all the help!!!! I will try this out and report back.

    You guys rock!

+ 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