I'm wondering if the following challenge can be solvedwith VBA / macro's, or that it is too complex...
The following is a simplified version of a large sheet of data:
Challenge: each person needs to get one file, only containing their rows of data because they have to check theirs.
For reasons of portability, the other person's data needs to be deleted (so not filtered out and hidden)
Current manual solution for creating John's file:
1. custom autofilter on everthing not equal to John
2. delete the remaining data after autofilter
3. autofilter show all
4. file > save as > "book1 - john.xls"
5. open original book1 again, and repeat step 1-4 for Peter...Michael... etc
To what extent can this be automated??
I think step 1-3 are more simple than 4-5, but cannot even solve 1-3...
I'm curious! Thanks in advance,
BA
The following is a simplified version of a large sheet of data:
Book1.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Person | Data | ||||
2 | John | 50 | ||||
3 | Michael | 45 | ||||
4 | Michael | 40 | ||||
5 | Peter | 35 | ||||
6 | John | 30 | ||||
7 | Richard | 25 | ||||
8 | Peter | 20 | ||||
9 | Richard | 15 | ||||
10 | John | 10 | ||||
Sheet1 |
Challenge: each person needs to get one file, only containing their rows of data because they have to check theirs.
For reasons of portability, the other person's data needs to be deleted (so not filtered out and hidden)
Current manual solution for creating John's file:
1. custom autofilter on everthing not equal to John
2. delete the remaining data after autofilter
3. autofilter show all
4. file > save as > "book1 - john.xls"
5. open original book1 again, and repeat step 1-4 for Peter...Michael... etc
To what extent can this be automated??
I think step 1-3 are more simple than 4-5, but cannot even solve 1-3...
I'm curious! Thanks in advance,
BA