Hi Everyone,
I am new to VBA code and was tasked with finding a way to create a macro that would look at a list in a worksheet and create a new workbook for each unique name in the list (column b) but also copy an entire sheet as a new worksheet for each dept (column a) that corresponds to the person's name in column b.
For example I have two tabs or sheets in an excel workbook named 1) Template 2) List
The list tab gives information as follows:
Column A (Dept)
2
3
4
5
6
7
8
9
10
Column B (Name)
Joe
Jane
Josh
Josh
Josh
John
John
Mike
Bill
Cindy
The template tab has information that will be populated via the department number in D2.
So I need the macro to do two things:
1) I need it to run through this list and create a workbook for each Unique Name
2) I need it to then copy the template worksheet (everything in the worksheet) into the newly created workbook for as many departments as the unique name has, with the new worksheet being named after the departments and the department number populating into the D2 cell in the copied template.
Example:
Create workbook called Josh, within that workbook there are three sheets labeled 3, 4, 5 for his corresponding departments and each of the sheets has all the information from the original copied template and 3,4,5 appear in each sheets D2 cell. Save workbook to My documents
Let me know if you need more information or clarification. Any help would be much appreciated. I have started trying to form code but nothing is producing what I need it to and have been coming up empty handed.
I am using excel 2007.
I am new to VBA code and was tasked with finding a way to create a macro that would look at a list in a worksheet and create a new workbook for each unique name in the list (column b) but also copy an entire sheet as a new worksheet for each dept (column a) that corresponds to the person's name in column b.
For example I have two tabs or sheets in an excel workbook named 1) Template 2) List
The list tab gives information as follows:
Column A (Dept)
2
3
4
5
6
7
8
9
10
Column B (Name)
Joe
Jane
Josh
Josh
Josh
John
John
Mike
Bill
Cindy
The template tab has information that will be populated via the department number in D2.
So I need the macro to do two things:
1) I need it to run through this list and create a workbook for each Unique Name
2) I need it to then copy the template worksheet (everything in the worksheet) into the newly created workbook for as many departments as the unique name has, with the new worksheet being named after the departments and the department number populating into the D2 cell in the copied template.
Example:
Create workbook called Josh, within that workbook there are three sheets labeled 3, 4, 5 for his corresponding departments and each of the sheets has all the information from the original copied template and 3,4,5 appear in each sheets D2 cell. Save workbook to My documents
Let me know if you need more information or clarification. Any help would be much appreciated. I have started trying to form code but nothing is producing what I need it to and have been coming up empty handed.
I am using excel 2007.
Last edited:
