Multi sheet population

[jeffwest1]

I have used VBA in Excel, but not for a while, so excuse the rather long winded question.

I have a crystal report that quite happly will export out the data I need into excel, this data contains a single entry for a number of users, each with a unique number.

I want to add the data into individual spreadsheets for those people, so want to have a macro to examine the export data, and populate a series of spreadsheets from that data based on a unique identifer.

Although for January I may have 75 people, Feb I will have 100, while these are in identifer number order becase there may be gaps i.e.
Jan 1,2,3,5,7,9
Feb 1,2,4,5.6.7.9

I cannot just hardcode links from output spreadsheet to individual spreadsheet, as they may not be in the same place each time, anyone know or give me a clue as to how VB code can do a search and populate accordingly.


--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

and BTW, If you get six Date Set reports each month from CR, you could EASILY get ONE CR report containing the six, and with real dates, unless you have a lazy, unresponsive CR programmer.

Now is not ONE simpler to handle than SIX?

And, oh yes, you will be APPENDING that data into your new source table each month, and that could even be automated, if necessary.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

The 'lazy unresponsive CR programmer' is me , at the moment having been looking at this during the writing, and was already thinking that a single file created would be better.

At the moment i have some test data in an Access database, this is creating individual exports, but each can be changed to an append query, but again, will need to be acreful that if it finds data for a givenm month already, it doesn't add more, but deletes and readds this.

The problem I have is that each calcualtion requires a different set of parameters, oh it just gets better, so I can't run them all at once, however, I can look at a way of during the export from crystal appending them instead.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

BTW, Your posted report could be generated by a PivotTable, if the source data were properly and fully populated. It would be simpler than a piece of cake.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

Unfortunatly, for each staff member there is no gaurantee that they work in all 6 of the client groups, some work only with one group not the other 5, some only in a few, some in all 6, others only on a shortterm basis, to cover for sick/annual leave or when that person first becomes into that group, then gets reassigned to a specialist, which is the other reason why the target is going to be manually added by team managers.

I did think about a pivot table, but as there is other data added to the staff sheet, absence, team manager observed behaviour against competences and other stuff, which is why i need the data to populate from a main table, rather than just export the data then pivot table it.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

Why should that make a difference, as long as ALL you data is in your source?

This is a better data structure that is more useful...
[tt]
Mon ID Data_Set DataTyp DataVal
Feb-09 1 Data Set 1 Target 45
Feb-09 8 Data Set 1 Target 55
Feb-09 11 Data Set 1 Target 65
Feb-09 14 Data Set 1 Target 75
Feb-09 24 Data Set 1 Target 85
Feb-09 28 Data Set 1 Target 95
Feb-09 47 Data Set 1 Target 105
Feb-09 1 Data Set 1 Actual 56
Feb-09 8 Data Set 1 Actual 23
Feb-09 11 Data Set 1 Actual 35
Feb-09 14 Data Set 1 Actual 8
Feb-09 24 Data Set 1 Actual 9
Feb-09 28 Data Set 1 Actual 16
Feb-09 47 Data Set 1 Actual 18
[/tt]
PivotTable
[tt]
ID 1

Sum of DataVal Mon DataTyp
Feb-09
Data_Set Actual Target
Data Set 1 56 45
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}