Auto update and format pivot table from Excel import

[darkfalcon885]

Hello,

I have no idea if this is possible, but I thought I would ask. There is a website that tracks people my company trains for a position. I am able to go to the site and export an excel file for the people certified. The file has some columns I don't need. Usually, I download the file, remove the columns and create a pivot table from the information. Then I conditionally format the pivot table to highlight any certs due within a month and within 6 months. Is there any way to automate any of these things. I am not very familiar with VBA but am a quick learner. This is not a file that I will keep on hand it is a file that I will download as it changes. I have also tried to set the information to auto download, but it ends up being an empty table I will also take any ideas on how to pull that off. Sorry if this is too many questions. 

[#2]

You should think about using Get Data (Power Query) for this. It will allow you to pick the CSV file for import, then apply steps such as removing unnecessary columns. When you finish all the steps to get the data you want, then it will load into the workbook and you can set up a PivotTable on it. Next time, if you save over the CSV file (new file with same name as the previous), you can simply press Refresh All in Excel to bring in the new data. You will also need to press the refresh button on the PivotTable after bringing in the new data.

To get started, just go to the Data tab -> From Text/CSV, then pick your CSV file.
Then click the Transform Data button to be brought into the Power Query editor, where you'll find all the ways that you can transform your data. Browse the buttons on the ribbon tabs. You'll notice Remove Columns.

The great thing about setting up your data import this way is that the steps are recorded so they'll be automatically applied next time your CSV is updated and you press Refresh All.