Macro - Open worksheets and archive rows

[aiwnjoo]

Hi all,

I have a requirement to archive some workbooks for employees. I will run through the scenario first.

Manager.xlsm - Macro workbook which has tabs named with each employee name;

Tab names;
Overview - This is an overview sheet which pulls in data from the employee tabs, nothing special here.
Macros - This is where there are buttons for each employee, these buttons run the macros for each employee.
EmployeeName1 - This is the tab for the first employee
EmployeeName2 - This is the tab for the second employee
EmployeeName3 - This is the tab for the third employee
And so on...

Each employee has their own sheet where they store data, the existing macro uses the tab names and sheet names to detect which employee we want to pull data for the pastes the values into the relevant tab above.

The employees worksheets and the tabs in Manager workbook match exactly (columns, formatting etc etc) the only difference is that the employee worksheets have workbook protection and conditional formatting to stop them causing the macro to fail by changing the format.

This all works well, the Manager workbook pulls all employee data in, nice and clean execution then the Overview sheet shows an overview of all employees for reporting.

Here is the macro, each employee has a different macro pointing to their sheets.

Please Login or Register  to view this content.

Now what I need to add to this is an archiving option but I want to run this as a seperate Macro because the above macro is ran several times a day but the archiving we will only need to do once a week/month or so.

I want to create some more tabs within the Manager sheet i.e. Employee1Archive then a macro will run and open the employees worksheet, unprotect it then check column A for any row that has "COMPLETE" in the cell, if it does it will copy the rows and paste values to Employee1Archive then delete the row in the users sheet, reprotect, save and close. Also it should do this without causing empty formatting at the bottom of the sheet (Where scrollbar continues past last used row, not sure how to explain that).

Each time the macro runs it will paste into Employee1Archive from the last row so not to overwrite data.

I hope this is clear enough. I have sample sheets if it helps. Thanks

[aiwnjoo]

Anyone? Thanks

[aiwnjoo]

Bump. Thanks

[aiwnjoo]

Bump. Thanks

[aiwnjoo]

Wonder how many bumps i can reach before someone actually helps.