+ Reply to Thread
Results 1 to 13 of 13

Loop through files in folder, copy range, Paste range to original workbook

  1. #1
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Loop through files in folder, copy range, Paste range to original workbook

    Hi,

    I'm trying to create a macro that loops through all files in a folder.

    During the loop I would like to use a for each statement:

    For Each workbook in folder

    - select the range("A10:AE" & Rows.Count).End(xlUP).Count
    - Copy the range
    - avoid the clipboard (using Destination:= WorkbookName)
    - Paste to the workbook that contains the macro Range("A:A" & Rows.Count).End(xlup).count
    - close workbook

    Next Workbook

    So far my code allows me to select a specified folder but does not execute the loop properly (no data gets copied and pasted)

    Any help would be greatly appreciated, thank you.

    Please Login or Register  to view this content.

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

    Re: Loop through files in folder, copy range, Paste range to original workbook

    You're interchanging throughout the code the two variables strFile and Filename. Use one or the other for the file name.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

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

    Re: Loop through files in folder, copy range, Paste range to original workbook

    I noticed a few other problems as well. Try this...

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Loop through files in folder, copy range, Paste range to original workbook

    Thank you AlphaFrog, much appreciate.

    The changes you have made execute the macro but it returns " 0 files have been added to the Monthly Summary Report".

    It doesn't copy the data from the files within the selected folder. Any ideas what might I might be missing within the loop?

    Please Login or Register  to view this content.

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

    Re: Loop through files in folder, copy range, Paste range to original workbook

    The problem may be with the procedure GetFolder. You didn't show that code. Is it returning the selected path?

    Does the selected folder have .xlsm files as apposed to .xlsx
    Last edited by AlphaFrog; 03-05-2014 at 04:17 PM.

  6. #6
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Loop through files in folder, copy range, Paste range to original workbook

    The selected folder contains .xlsx file types.

    I will attach the file where the macro is stored and a file from the folder that is selected(changes each week).

    Once again thank you for the help.

    Here is GetFolder() that I'm currently using.

    Please Login or Register  to view this content.

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

    Re: Loop through files in folder, copy range, Paste range to original workbook

    Change this...

    strFile = Dir(strPath & "*.xlsx")

  8. #8
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Loop through files in folder, copy range, Paste range to original workbook

    I made sure that all file types in the code were adjusted correctly. I made sure the sheet names were correct and it still completes the macro but adds nothing.

    Is it possible that the copy and paste section of the loop is missing something?

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

    Re: Loop through files in folder, copy range, Paste range to original workbook

    Quote Originally Posted by knevil View Post
    I made sure that all file types in the code were adjusted correctly. I made sure the sheet names were correct and it still completes the macro but adds nothing.

    Is it possible that the copy and paste section of the loop is missing something?
    The code works for me using your example files. I'm guessing no files are not getting opened for some reason.

    Are you selecting the correct folder?

    If you want to show your modified code to confirm the file name extension change.

    Do you know how to step through the code and look at the variable values as the code progresses?

  10. #10
    Registered User
    Join Date
    07-12-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Loop through files in folder, copy range, Paste range to original workbook

    Thanks again for helping me out.

    Here is the code once. The folder that's selected contains xlsx files and the Workbook containing the macro is obviously xlsm. At the bottom is the getfolder function.

    Please Login or Register  to view this content.
    Get folder
    Please Login or Register  to view this content.

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

    Re: Loop through files in folder, copy range, Paste range to original workbook

    In post #2 I explained what you had done wrong in your original code. Then I gave you whole new code in #3 to fix other problems.

    Your recently posted Non_AD_Summary is your original code that didn't work. The only change is the file extension.

    Use the code in #3 and change the extension to "*.xlsx"

  12. #12
    Registered User
    Join Date
    01-29-2016
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    39

    Re: Loop through files in folder, copy range, Paste range to original workbook

    Hi All,

    Thanks in advance .

    looping throught the files create a master file ...VBA

    i have created a folder and pasted 10 excel files which has same format(Headers)(*.xlsx format) and each file has four sheets with Different names example : xx ,yy,zz,AA

    now i wanted to make a master file of those 10 excel files and master file should have sheet names xx,yy,zz,AA , looping through the each excel file xx sheet data should copied and pasted in master file xx sheet ,like yy to yy , zz to zz and AA to AA.

  13. #13
    Registered User
    Join Date
    01-29-2016
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    39

    Re: Loop through files in folder, copy range, Paste range to original workbook

    Hi All,

    Thanks in advance .

    Looping throught the files create a master file ...Macro or VBA

    I have created a folder and pasted 10 excel files which has same format(Headers)(*.xlsx format) and each file has four sheets with Different names Example : xx ,yy,zz,AA (Worksheet names)

    Now i wanted to make a master file of those 10 excel files and master file should have sheet names xx,yy,zz,AA , looping through the each excel file xx sheet data should copied and pasted in master file xx sheet ,like yy to yy , zz to zz and AA to AA.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy/paste Range of worksheets of several files in a folder to a master file
    By mrjinx007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-18-2013, 01:02 PM
  2. Open Multiple Files, Copy Worksheet, Paste in to Specific Tab in Original Workbook
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-30-2013, 10:27 AM
  3. Copy a Template workbook ans paste in a multiple Files in a Folder
    By arvind1979_3 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-24-2013, 09:07 AM
  4. Copy a row from 3000 excel files stored in a folder and paste it in a new workbook
    By hubertus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-28-2009, 10:34 AM
  5. Copy a range and paste into another workbook - files are password protected.
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-20-2008, 06:57 AM

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