Help with Vba, Data to table ready for access upload

Hi

I've and managed to bodge this together but there are a couple of thing im not happy with and i dont have a clue how to solve them

The Original data has the following headings in excel

EmployeeName|Skill|Team|1/1/15|2/1/15 (Etc x364)

the planners are using data validation lists with the activities and merfing the cells across the range required.

What i think this is now doing(What i would like it to do)
Its taking a schedule containing the merged cells, removing and replacing the data in the footprint of the merged cell,
finding all blank cells within the range and putting the formula in to show "Free" cells
pasting values in seperate workbook
taking the data and placing into list view with the following column headings

ID|EmployeeName|Skill|Team|Date|Data|ExportDate(now()

Please Login or Register  to view this content.

I have a macro recorded to fire it into an access database but this also has an error?

I need the code in told to be instead of selecting the range "[Sheet1$A1:c50]" it selects the active sheet and the finished range from above (note when converting to the table vew above it extends past 1000 rows

Please Login or Register  to view this content.

any help would be appreciated

Thanks

Rob