Help with importing sheet & converting to table

[Jamesraywebber85]

I have a workbook called "Team Numbers" that has pivot tables from the sheets named after a report. The reports are called: "CC Closed This Year", "AR Closed This Year", "Inbox Closed This Year" & at the end of each file has the time the report was ran.

I would like a code for a button that will import those 3 reports on each sheet & then format them as a table so the pivot table is automatically updated. My tables does not have a month column so I added it with the formula

Please Login or Register  to view this content.

at the end of each table so I can pivot by month as well.

Sorry I had to delete ALOT of info on the reports & one of the pivot tables because the form only allowed me to upload 1000KB at 1 time. You will see the report name & the name of the sheet that should have the corresponding data. If you have any questions please ask, I try to respond immediately as i keep an eye on my email constantly.

I have uploaded all the files including my workbook for any help that you can provide. It would be greatly appreciated! Thank you in advanccredit card

[dflak]

I did a bit of reorganizing. I changed the table names in the Team Numbers file to reflect the data in the table. For Example Table_CC_Closed_This_Year. I made the table name "match" the sheet name and the naming convention made some of the coding easier.

Also I didn't understand the complicated formula for you to get the date. If you simply want the month name of ANY date, use =TEXT(Date, "mmm").

Put this file anywhere on the hard drive. Make two subfolders: Data and Archive.

When you download the data, put it in the Data Folder and do NOT use the date stamp. E.g use AR Closed This Year.xlsx instead of AR Closed This Year-2023-05-04-08-46-51.xlsx. Yes, you will overwrite the old data each time you download.

When you click on the Import Data button, the program will check that the data files exist and are named properly. The flag in cell B5 indicates if an error is found. You will not be able to proceed until you have properly named file names in the Data folder.

The next thing that the program does is make a copy of the data file, stick on a date stamp and put it in the archive folder. The archive folder is where you can keep your history.

Other than that, the program clears the tables and copies in the data. Then it refreshes all the pivot tables.

[Jamesraywebber85]

OMG this is perfect! Thank you so much!!!

[Jamesraywebber85]

I have added a new sheet called "Reworks" but it is not importing. Any advice? I have attached the updated files for any help!

Reworks.xlsx
Team Numbers.xlsb
AR Closed This Year.xlsx
Inbox Closed This Year.xlsx
CC Closed This Year.xlsx