Hi, I'm hoping someone can help me with something that is proving a little beyond my knowledge of excel.
I have a number of people complete a workbook for me, using a check sheet. this is all vehicle related inspections. the data is split into 2 types, static data, and defect data.
When they complete the workbook, a macro runs that takes the captured data, and copies to 2 seperate worksheets, one called inspection data, this is one row of static data from column B to Column AO, column a is blank, titled inspection ID
The defects data is copied to a worksheet called defects, this can be multiple rows made of up of 5 columns, 1 row for each defect found. column a is blank, titled inspection ID
This all works perfectly.
Now my goal
I want to have these files copied by the inspectors, into a specific folder, within this folder will be a workbook, that temporarily I am calling database. The format of database is made up of 2 tabs, identical to inspection data and defects from the inspections sheets.
What I want is a macro in the database workbook that will do the following:
import the data from each workbook copied to the folder
assign a new id in column a on both the inspection data worksheet and the defects worksheet, the id must match each other. so e.g. a single workbook is imported, and id 1 is assigned to column a on inspection data, and also id 1 is assigned to all rows in column a on defects
the next workbook is imported, and id 2 assigned, etc, etc. the ids should be sequential.
this id is to make sure when i do my reporting dashboard i can tally up the defects to the static data and report on specific inspections
once all workbooks in the folder are imported, the files then need to be moved to a sub folder called "imported"
on the next day, if more inspections are done, and the files copied to the folder, I want my user to open the database workbook, run the import macro and the data appended to the existing data, with the next sequential id
I really dont know if this is possible but would be fantastic if anyone can help.
TIA
Jade
I have a number of people complete a workbook for me, using a check sheet. this is all vehicle related inspections. the data is split into 2 types, static data, and defect data.
When they complete the workbook, a macro runs that takes the captured data, and copies to 2 seperate worksheets, one called inspection data, this is one row of static data from column B to Column AO, column a is blank, titled inspection ID
The defects data is copied to a worksheet called defects, this can be multiple rows made of up of 5 columns, 1 row for each defect found. column a is blank, titled inspection ID
This all works perfectly.
Now my goal
I want to have these files copied by the inspectors, into a specific folder, within this folder will be a workbook, that temporarily I am calling database. The format of database is made up of 2 tabs, identical to inspection data and defects from the inspections sheets.
What I want is a macro in the database workbook that will do the following:
import the data from each workbook copied to the folder
assign a new id in column a on both the inspection data worksheet and the defects worksheet, the id must match each other. so e.g. a single workbook is imported, and id 1 is assigned to column a on inspection data, and also id 1 is assigned to all rows in column a on defects
the next workbook is imported, and id 2 assigned, etc, etc. the ids should be sequential.
this id is to make sure when i do my reporting dashboard i can tally up the defects to the static data and report on specific inspections
once all workbooks in the folder are imported, the files then need to be moved to a sub folder called "imported"
on the next day, if more inspections are done, and the files copied to the folder, I want my user to open the database workbook, run the import macro and the data appended to the existing data, with the next sequential id
I really dont know if this is possible but would be fantastic if anyone can help.
TIA
Jade
