$70 USD: Import (copy) data from another workbook into Excel Table

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.

Thank you!

Thank you KJ !! Let me know if you have any ?'s

Thank you too Ger!

It will be the same Directory but different folder.

Snapshot Address:
C:\Users\jstriker\Documents\Alix Partners\Weekly Dashboard\Snapshot Export

Weekly Dashboard Address:
C:\Users\jstriker\Documents\Alix Partners\Weekly Dashboard

No problem. I appreciate your help

When the data is imported what happens to the URLs in Column 1 of Table1? The URL's is a formula based off the ID in Column B so when data is pasted in Column B, the URL's will be updated based on the new values.

If the imported data has less rows than the current data in Table1, are the extra URLs deleted? Yes, if they will be deleted.

If the imported data has more rows than the current data in Table1, do additional URLs need to be inserted with the ending number incremented by 1 for each new URL? Yes, they need inserted but when you insert rows, the formulas automatically get populated since it is a table.

Excellent. I'll get payment out when I get the info.

Thank you Kj...I don't see the file in your post.

Thank you!!!! I will let you know if I have any ?'s. Now go get some rest!!!