Automate Data Transfer workbook to 500 workbooks

Hi Guys,

I am looking to create a macro which will pull data from one workbook, one record at a time, and insert and save it in another workbook.

Each record will be saved in it's own workbook

there are 500 records in the main workbook which need to be moved to 500 separate workbooks.

the format for transferring the data is as follows:

(Each Record will have its own row, eg Column A means Column A, Row of record)
(all Records on the Record sheet are entered into Column C, eg Row 11 = Cell C11)


Main Sheet Column A -> Record Sheet Row 3/4(merged)
Main Sheet Column B -> Record Sheet Row 6/7(merged)
Main Sheet Column C -> Record Sheet Row 11
Main Sheet Column D -> Record Sheet Row 9
Main Sheet Column E -> Record Sheet Row 13
Main Sheet Column F -> Record Sheet Row 17
Main Sheet Column G -> Record Sheet Row 19
Main Sheet Column H -> Record Sheet Row 23
Main Sheet Column I -> Record Sheet Row 15
Main Sheet Column J -> Record Sheet Row 25
Finally on Record Sheet Row 21need to enter formula "=EOMONTH(C19,C23-1)"

The Records stored on the main sheet are not spaced with one record per line, there is usually one record every 2 lines but this is not always the case. Ideally the function needs to say:

"If there is something in column A, Row number, then run the code, if there isn't then don't run the code"

The Main Sheet is at location C:\Documents\MainSheet.xls

The Record Sheets are located in C:\Documents\

They are named in ascending order so perhaps a loop could be used:

Copy (1) of Account.xls
Copy (2) of Account.xls
Copy (3) of Account.xls
......
......
.......
Copy (498) of Account.xls
Copy (499) of Account.xls
Copy (500) of Account.xls

I have attached a workbook to give you an ides. Main Sheet is on Sheet1, record sheet is on sheet 2. it is just to give you an idea of layout, the files are separate workbooks on my system.

Any Help will save me hours of work

many many thanks