R Davignon
asked on
Populate Excel sheets based on criteria from import sheet using VBA
I have an excel workbook with a worksheet call "Import". The data is refreshed from a sharepoint list. The data structure is similar to this:
ID Loc Start Date End Date Group
44 CA 5/6/2017 5/8/2017 A
33 TX 6/8/2017 6/10/2017 B
34 NM 9/1/2017 9/3/2017 C
33 DC 9/1/2017 9/3/2017 B
33 TX 6/8/2017 6/10/2017 B
34 NM 9/1/2017 9/3/2017 C
33 DC 9/1/2017 9/3/2017 B
33 TX 6/8/2017 6/10/2017 B
34 NM 9/1/2017 9/3/2017 C
33 DC 9/1/2017 9/3/2017 B
I would like to create VBA to go through each imported row of data and populate 3 additional worksheet tabs (Group A) (Group B) and (Group C) with only the matching criteria.
For example Worksheet labeled "Group B" be recreated and look like this:
ID Loc Start Date End Date Group
33 TX 6/8/2017 6/10/2017 B
33 DC 9/1/2017 9/3/2017 B
33 TX 6/8/2017 6/10/2017 B
33 DC 9/1/2017 9/3/2017 B
33 TX 6/8/2017 6/10/2017 B
33 DC 9/1/2017 9/3/2017 B
Thanks for any help//suggestions.
ID Loc Start Date End Date Group
44 CA 5/6/2017 5/8/2017 A
33 TX 6/8/2017 6/10/2017 B
34 NM 9/1/2017 9/3/2017 C
33 DC 9/1/2017 9/3/2017 B
33 TX 6/8/2017 6/10/2017 B
34 NM 9/1/2017 9/3/2017 C
33 DC 9/1/2017 9/3/2017 B
33 TX 6/8/2017 6/10/2017 B
34 NM 9/1/2017 9/3/2017 C
33 DC 9/1/2017 9/3/2017 B
I would like to create VBA to go through each imported row of data and populate 3 additional worksheet tabs (Group A) (Group B) and (Group C) with only the matching criteria.
For example Worksheet labeled "Group B" be recreated and look like this:
ID Loc Start Date End Date Group
33 TX 6/8/2017 6/10/2017 B
33 DC 9/1/2017 9/3/2017 B
33 TX 6/8/2017 6/10/2017 B
33 DC 9/1/2017 9/3/2017 B
33 TX 6/8/2017 6/10/2017 B
33 DC 9/1/2017 9/3/2017 B
Thanks for any help//suggestions.
ASKER
Worked like a charm, however when I added additional columns of data I kept getting a VBA 400 error. I kept the Group in column E, howevered added about 10 columns to the right. Columns F-Column P. Would that be the cause of the problem?
No. That shouldn't be the case.
Did you tweak the code at your end?
It would be helpful if you can upload the workbook along with the code in question.
Did you tweak the code at your end?
It would be helpful if you can upload the workbook along with the code in question.
ASKER
I have attached a worksheet that includes your code as well as a couple of lines with the import tab as it comes in from our share point site.
ID Loc Start End Group
17 TX 7/25/16 8/24/17 A
18 CA 6/24/16 6/29/16 B
19 CA 7/25/16 8/24/17 C
20 TX 6/24/16 6/29/16 D
21 CA 7/25/16 8/24/17 A
22 CA 6/24/16 6/29/16 D
23 TX 7/25/16 8/24/17 B
24 CA 6/24/16 6/29/16 C
25 CA 7/25/16 8/24/17 D
26 TX 6/24/16 6/29/16 D
27 CA 7/25/16 8/24/17 D
28 CA 6/24/16 6/29/16 D
29 TX 7/25/16 8/24/17 D
30 CA 6/24/16 6/29/16 B
31 CA 7/25/16 8/24/17 D
32 TX 6/24/16 6/29/16 C
33 CA 7/25/16 8/24/17 D
34 CA 6/24/16 6/29/16 D
35 TX 7/25/16 8/24/17 D
36 CA 6/24/16 6/29/16 D
37 CA 7/25/16 8/24/17 D
38 TX 6/24/16 6/29/16 D
39 CA 7/25/16 8/24/17 C
40 CA 6/24/16 6/29/16 D
41 TX 7/25/16 8/24/17 D
42 CA 6/24/16 6/29/16 D
43 CA 7/25/16 8/24/17 D
44 TX 6/24/16 6/29/16 D
45 CA 7/25/16 8/24/17 E
SampleData.xlsm
ID Loc Start End Group
17 TX 7/25/16 8/24/17 A
18 CA 6/24/16 6/29/16 B
19 CA 7/25/16 8/24/17 C
20 TX 6/24/16 6/29/16 D
21 CA 7/25/16 8/24/17 A
22 CA 6/24/16 6/29/16 D
23 TX 7/25/16 8/24/17 B
24 CA 6/24/16 6/29/16 C
25 CA 7/25/16 8/24/17 D
26 TX 6/24/16 6/29/16 D
27 CA 7/25/16 8/24/17 D
28 CA 6/24/16 6/29/16 D
29 TX 7/25/16 8/24/17 D
30 CA 6/24/16 6/29/16 B
31 CA 7/25/16 8/24/17 D
32 TX 6/24/16 6/29/16 C
33 CA 7/25/16 8/24/17 D
34 CA 6/24/16 6/29/16 D
35 TX 7/25/16 8/24/17 D
36 CA 6/24/16 6/29/16 D
37 CA 7/25/16 8/24/17 D
38 TX 6/24/16 6/29/16 D
39 CA 7/25/16 8/24/17 C
40 CA 6/24/16 6/29/16 D
41 TX 7/25/16 8/24/17 D
42 CA 6/24/16 6/29/16 D
43 CA 7/25/16 8/24/17 D
44 TX 6/24/16 6/29/16 D
45 CA 7/25/16 8/24/17 E
SampleData.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It worked perfectly! Thank you for all your help. I appreciate your assistance.
You're welcome. Glad I could help. :)
In the attached, click the button on Import Sheet called Split Data to run the code to get the desired output.
Open in new window
Split-Data.xlsm