I have several files all identical in format, in a single target directory,
single sheets with different names, e.g.:
foc_1200.xls
foc_1250.xls
foc_1300.xls
foc_1350.xls
foc_1400.xls

I want to import the complete contents of each file into one workbook, with
a seperate worksheet for each named as follows:
-100
-50
0
+50
+100
where the contents of foc_1200.xls would go into sheet "-100", foc_1250.xls
into sheet "-50" etc, overwriting whatever is there.
I would like to do this using a macro where I only need to input the name of
the first file, in this case foc_1200.xls, and maybe the offset (50) to the
next file and have the macro basically cut and paste instead of having to do
this manually.
There would not necessarily be an offset of 50, it might be 100, in which
case I need the offset maintained in the destination workbook, i.e. if I
instead have three files in the target folder with a 100 offset:
foc_1415.xls
foc_1515.xls
foc_1615.xls
then the data should be pasted into only worksheets -100, 0 and +100,
leaving sheets "-50" and "+50" unchanged. (or blank if that's easier)
The offset would always be constant, but it might be 50 or 100 or an
arbitary number. I would enter that manually.
Any ideas?