Cycle through workbooks in a folder
Posted on 2012-09-09
This may not be possible using vba in Excel but as my grandad used to say, "if ya don't ask, ya don't get!"
I have a specific folder "WeeklyCeva", that workbooks are dumped into every week. The number of workbooks vary from 6 to 20 and somtimes more. The names of the workbooks vary a lot too. The only constant that I can see is that they all have the suffix ".xlsx". There are no other files of any type in this folder.
All the workbooks in the folder have just one sheet with data, named "Sheet1", (I know.., it's silly).
I have a specific workbook called "CevaCurrent" on my computer. I first delete all the existing sheets except sheet one, but I clear the contents of sheet 1. I then copy and paste the values of all the sheet1's, from all the workbooks in folder "WeeklyCeva", to their own sheet in "CevaCurrent".
Once this is done I have macros set up to collate all the relevant data from all the sheets in "CevaCurrent" onto one sheet into a final workbook.
Is there a way of automating this first sequence using VBA?
That is to say;
Delete the sheets in "CevaCurrent" and clear the contents of remaining sheet, "sheet1"; Cycle through all the workbooks in folder "WeeklyCeva"; copy the whole of sheet1 from each of these workbooks; then paste the values into new consecutive sheets in the workbook named "CevaCurrent"?
I have looked on here but cannot see anything that I could do some jiggery-pokery with to make it work.