The idea is that we will receive several excel spreadsheets from different introducers. I thought that, so long as they are all in the same format, I could just transfer the data to my "import spreadsheet"
What I was saying is that your "import spreadsheet" is a middleman. It neither adds nor subtracts value to this process, strictly speaking. Nothing originates or terminates in it.
My comment was to take the individual workbooks (that you now have explained come from your "introducers" - whatever that means) as your direct inputs. Transferring these files into a common workbook only to have to delete the rows of the common file later is an example of ineconomic process design that can introduce procedural errors.
Where I am coming from is the number of steps it takes to do something simply yet safely. Think "motion studies" - sort of akin to ergonomic studies to eliminate unneeded steps in a manual process. The Access analog to this is having too many data transfer steps. Each such extra step introduces possible errors in disposition of data, potentially resulting in data loss.
OK, so how do you avoid that and simplify your import process? Why, bless you, it's easy! But like anything worth doing, it takes a little bit of planning. And a little bit of programming. Pat's suggestion is part of what I would have suggested, but I think even she missed the inefficiency in your comments this time.
First, introduce your workbooks to a single directory. Name them according to some naming scheme that is right for you. Create a /USED sub-folder in that import folder. You said you have common structure among all the files, so there should be no problem with the mechanics of the import. One import routine should handle all such workbooks one at a time.
When you are ready to import, have some VBA code under an OnClick routine for a button on a form, maybe even an unbound switchboard form, that does the following:
1. Using the FileFind object, search for all workbooks in your data-import source directory. The place where the workbooks get dumped when your introducer introduces them, I guess.
2. For each workbook, perform an import through VBA. Since you can do this manually, you can program it using the TransferSpreadsheet method of the DoCmd object. Or, if you had a subroutine in VBA to do this with Excel Application Objects, use it instead. Six of one, half-dozen of the other.
3. When done with the spreadsheet in your VBA code, use the NameAs function to alter the workbook's name to include some tag that lets you know you have used it. Also, you can use NameAs to move a directory pointer on the same volume. So when you rename the used workbook, also rename it into the /USED sub-folder. Pat's suggestion of a date tag is as good as any other. If in doubt, look at the Format function to create a date tag of the form yymmdd using user-defined format strings. The Help file can help you here.
4. When the FileFind's .Found collect is empty, you are done and the directory is reset. You don't have to reset rows in a spreadsheet. You just move the workbook. And a NameAs, being a rename, doesn't waste time copying the file. It just mucks the file pointer.
5. Your backup process can capture copies of everything in the /USED directory with whatever frequency is appropriate. (You DO have a regular backup process in place for your production date, don't you...?)
6. At some regular interval, when you are satisfied that your backup is good and your raw workbooks are obsolete, purge that sub-folder by hand. Put it on your calendar.
Now, as to transferring the data one spreadsheet at the time, it would seem from your comments and questions that you know how to do that, but if you really don't (because I mis-read something), please ask.
