VBA read all Excel files in a directory then parse through each

[kpierce]

Hello -


I am needing a script in Excel that I can press a button and it will ask for a folder name, then read through all the XLS files in the folder one by one and step through the individual tabs writing a segment from each tab to another spreadsheet



For example: I have 123.XLS, 456.XLS and 789.XLS all in one folder

123.XLS

Sheet1

Sheet2

Sheet3

456.XLS

SheetA

SheetB

789.XLS

SheetX

SheetY

SheetZ



MACRO/Script would reside in ABC.XLS in another folder so it is not read.



I would like this script to first open 123.XLS, finding it has 3 sheets. I would for example to read A1 and place it back into ABC.XLS, then go to sheet2 and read A1 and place it back into ABC.XLS, then sheet3 and read A1 and place it back into ABC.XLS



Then go to the next XLS file.



I hope this makes sense.



thanks.

[Leith Ross]

Hello kpierce,

Welcome to the Forum!

This macro will let you choose the folder to copy from. Each "A1" value will be transferred to the active sheet starting with the next cell after the last entry in column "A".

Please Login or Register  to view this content.

[inshin]

Hi, thank you for this very useful code. Could you tell me how I would restrict the read to only specified worksheets in a workbook and also a specific range (A1:F1) as opposed to the single cell example in this code.

Thanks