KW,
Conceptually, here is what you should be trying to do:
Post #1:
VBA opens the Excel Spreadsheet
You have a pointer to Cell A1This Pointer (x) increments by Rows
It serves as a basis for another Pointer indicating the Sheet (x+1)
Designate the Source Sheet (Sheet[x+1])
Update Cell Ax with the value from Sheet[x+1].B4
Move to the next Row/Sheet (x = x + 1)
Some challenges with this: How many sheets do you have? Do you always have the same number of sheets? Do you have a standardized naming convention for these sheets or just standard "Sheetx"?
Post #2:
VBA Opens your Master Workbook (that saves the stuff you want to keep)
VBA looks in your designated directory for .xls[x] files
It opens the first file and looks for the data
It copies the data to the master file
It moves to the next file
Some challenges with this: How many files are there? How do you keep track of which files have been opened already? Do you delete the files afterward? how confident are you that they will all have the same identical format?
Just some things to think about. What you want to do is doable. What experience do you have with VBA automating MS Excel? This is what you will need to use.
34
