Hi.
I am currently trying to do ambtitious things with some data I get from the IT department at work. I will try and explain the steps involved in what I am trying to do:
1. We get the data in text files. I have created this macro that will allow me to open the data in excel in the format I want, so no problems here.
2. The data consists of around 15 columns. I need to add in a few extra columns with formulas to create new data columns based on the original data. so I end up with say 20 columns overall - 15 original and 5 manufactured.
3. The next step involves summarising all columns of the data to get subtotals etc. So, there is a couple of pivot tables on other sheets doing this.
4. This stage involves reading off the data from the pivot tables onto the sheets where we will be presenting the data. We are only interested in extracting the summarised data from the pivot tables. This makes use of the GETPIVOTDATA function. The data should now be presented in the forms as required.
OK, so what am I asking? Well, ideally, I want all of this combined into a SINGLE macro. Crazy? Oh yes I am.
I wonder if anyone has had similar big jobs to do. For next year's data I hope to have a system set up where by I can just get the text file from IT dept, then click a macro and ALL of this is done in one go.
Ambitious really isn't it, but I am convinced it can be done. But for it to be effective it will take a lot of clever, conscise programming and setting up I think.
Any advice or comments appreciated.
RET79
I am currently trying to do ambtitious things with some data I get from the IT department at work. I will try and explain the steps involved in what I am trying to do:
1. We get the data in text files. I have created this macro that will allow me to open the data in excel in the format I want, so no problems here.
2. The data consists of around 15 columns. I need to add in a few extra columns with formulas to create new data columns based on the original data. so I end up with say 20 columns overall - 15 original and 5 manufactured.
3. The next step involves summarising all columns of the data to get subtotals etc. So, there is a couple of pivot tables on other sheets doing this.
4. This stage involves reading off the data from the pivot tables onto the sheets where we will be presenting the data. We are only interested in extracting the summarised data from the pivot tables. This makes use of the GETPIVOTDATA function. The data should now be presented in the forms as required.
OK, so what am I asking? Well, ideally, I want all of this combined into a SINGLE macro. Crazy? Oh yes I am.
I wonder if anyone has had similar big jobs to do. For next year's data I hope to have a system set up where by I can just get the text file from IT dept, then click a macro and ALL of this is done in one go.
Ambitious really isn't it, but I am convinced it can be done. But for it to be effective it will take a lot of clever, conscise programming and setting up I think.
Any advice or comments appreciated.
RET79