I have a workbook "A" which is regularly being updated and renamed.
This workbook contains transaction records organised into different worksheets for each quarter, but the data in each sheet is arranged similiarly.

As new data is being added, the old records are copied into another workbook "B" which has a fixed name and contains all the archived records on one "data" worksheet.

Each transaction record is identified by it's unique 4 digit transaction number in the first cell (in column A)

Is it possible to create a VBA script in Workbook "A" that can do perform the tasks below?

1. Unhide all hidden columns, rows in the active sheet (and setting all filters to "show all")

2.copy all data within a dynamic range of cells in the active sheet (say from row 2 to the the last non blank row within column A to Y)

3. open workbook "B" and update the "Data" worksheet by: a) creating a new record by copying a record onto a blank row if it does not already exist; or b) replacing a record which already exist by overwriting it with the current version in Workbook A.

The the ability to quickly update Workbook B with the latest changes in Workbook A would make any analysis of the historical data using Pivot table more accurate.

Has anyone here done anything similar in the past?

Thanks.