How to detect data ranges, and move data between worksheets

[oOarthurOo]

Greetings, each month I perform a repetitive task on about 50 workbooks similar to the example one I've included. My task is to select all data and formatting in 'Current', copy and paste 4 rows below the last row with data in 'Archive'. Then I return to 'Current' and clear out the data in columns G through K (excep column headings that is). Lastly, I update the date ranges in those same columns, and change the month in the date cell.

I hope to create a macro to automate some of these tasks. For instance, I would be very nice if I could run a script that would copy the data range in 'Current' over to the bottom of 'Archive' for every workbook in a specific folder. That alone would save me a great deal of time.

I've tried recording macros to do this, but it tends to only work on the one workbook and if I add or remove rows this also tends to muck up the macro. Any solutions or advice is greatly appreciated.

A.M.