Ozgrid Pre-payment has been sent.
I have a weekly report (see attached file - Weekly Report_Import Data Sample.xlsm) that I generate that is based off of data that is exported to excel every week (see attached file - Snapshot Export Sample.xlsx). The data from the Snapshot file is manually copied and pasted into the Excel Table (Table1) located on the Snapshot tab of the Weekly Report. And I also have to add rows (and re-format new rows) to the table every week to accommodate the extra rows before pasting into the table. I was looking for a macro to to accomplish the following steps to streamline the process:
- Add a macro button to the Data tab of the Weekly Report_Import Data Sample file that will pull up a browser window so a Snapshot Export Sample.xlsx file can be selected and then imported or copied to the Table1 located on the Snapshot tab.
- The entire data set from the Snapshot Export tab beginning with cell A2 down the last row and through Column CH will be copied. The beginning row (2) is static and the column range A:CH is also static. But the number of rows will change (mostly increase week over week). The data should be pasted as values into cell B11 which is in the 2nd row of Table1.
- Is there a way that the macro could count the number of rows in the Snapshot Export source file (less the Header row) and compare that that to the amount of rows currently available in Table1? For example, in the attached Snapshot Export file contains 1,973 rows of data below Header. The attached Weekly Report_Import Data file has 1,968 rows of data below the Table Header row (and I always leave an additional blank row at the end of the table above the Total Row). Could a macro count the difference of the the amount of rows that need to be added (or subtracted) (5 for this example) and then insert that amount into the table? Then also applying the same format to the new rows as the existing rows have (I normally have to use the Format Painter option to copy format to new rows when I update manually).
- If this cannot be done in one macro could another be created to accomplish this prior to the import / copy procedure?
Let me know if you have any ?'s and I will get right back to you.