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.

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.

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.

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?

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.

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.

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.