Copy and pasta data but needs to filter through the column first

[dee2016]

Hi Guys,

I need to copy Column G in sheetAD ,in workbook1 to Column A in sheet1 in workbook2, but column G is a column that describes a department e.g packaging, cleaning, boxing ect.
This column also has a filter so you can choose to view the details of just one department.
I want to filter so i only see the details of packaging.
This then shows the relevant information in column AR that i wish to copy and paste into workbook2.
Column AR contains the hours worked by the packaging department, once I paste this data into Workbook2 , I want to get the sum of the total hours.

Each time this macro is run (once a day) I want the previous days data to be cleared and replaced with the new updated data.
I would like the macro to be in the destination workbook not the workbook that contains the data.

Thank you in advance, if possible could you guys put in small comments so i can learn and understand the code you suggest please

[dflak]

If you can attach the sample workbooks without sensitive data we might be able to help you out.