Inaccurate Results when Copying Data to a Table Containing Formulas

Hello,

I need assistance with how to copy a data table (or worksheet) to an existing table (or worksheet) on a separate tab in my WB and have this destination table format itself correctly. Each month I receive a new data set from an outside source that I compare to the previous data set from a month earlier (Sheet1 to Sheet2). The first function clears last month's (Old Data) from Sheet1 and copies Sheet2's content to the Sheet1 tab - leaving Sheet2 blank. I then manually copy the contents of the new outside source data (headers and all) to Sheet2 and run a compare macro. The prep and compare macros run fine, but the third function is where I run into problems.

As I also want to capture purchase order age data, I have inserted another worksheet; Raw Age Data, which includes formulas for calculating under 30 days, Over 30, 60, 90, etc., which I then pivot and dashboard for analysis. The problem is that the size (number of rows) of Sheet2 changes from month to month so when I run the macro to copy from Sheet2 to Raw Age it doesn’t adjust to accommodate the new number of rows. There are often blank rows in columns A-H with formulas in columns I-M and vice-versa. Sometimes the copy function populates I-H in ALL cells which is also undesired!

I have attached the workbook as well as a formatted New Outside Data Source file.

Thank you

jaslake,

Thank you for your assistance, but the code did not work. I copied the code that you provided into a new module, and ran it several times, but no data copied to the Raw Age sheet.

Duh! My mistake. The code works great. Thank you so much!!