VBA to create dashboard report for multiple employees from template

BillGinSC

New Member
Joined
Mar 30, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello-
I found an old post that accomplishes 80% of what I need. Could someone offer guidance on how to modify for the addition and change referenced?
Changes requested are:
  1. My data has employees across multiple regions. Is it possible to add criteria to that would initially filter the total employee population by region? Just to follow the logic of the information above, region would be listed in the worksheet titled "EE_List" in column C (also starting in row 2 with the other employee data). A dropdown list to allow a user to select the region is held in a separate worksheet titled "Input for Macro" in cell B1.
  2. Instead of creating a new worksheet for each employee, I would like to export to a pdf, with each pdf listing the employee name (which is in cell A2 of the dashboard report noted as "DB_template").
Thank you for any help!

Here is the old request:

I've created a single worksheet that is essentially a "dashboard" type of report that displays data for a single employee/person. This worksheet (let's assume it's my "template") contains formulae that is driven by a single lookup cell (in this case, the employee's ID, in cell, for example, A1).

This "dashboard" report (for just one individual) needs to be duplicated for the entire employee population (let's say ~100 people); this employee population is stored as a list in a separate worksheet, in a single column.

Is there a vba solution that will copy my template and populate the cell A1 (of each duplicated template worksheet) with the the employee IDs - of course, it would be ideal if the solution can search in my employee ID list and continue down until there are no more IDs and create the same number of worksheets as there are IDs?

Because the macro is creating numerous worksheets, is it possible to name the worksheets based off of an adjacent value (in the same sheet as the IDs) to each respective ID (I'm thinking of a concatenation of the employees' IDs and names)?

One major wrinkle in this process: the above process would be ideal if the "template" worksheet won't change (i.e. no changes to formatting, no additional data elements, etc.) However, it's likely that the client will want to add/remove/change items/elements to the "template" dashboard. Assuming, at this point, all 100 worksheets for each individual/employee have been created, it would be a huge pain to manually add/remove elements from each sheet. Is there another macro that would delete ALL employee sheets? That way, I can re-modify my single template and just run the original macro to re-create my duplicate sheets (but populated with different IDs) again.

1) The template sheet is called "DB_template"

2) I said A1 in my original post as example, but sure, A1 can be where IDs are populated
3) The sheet with the employee (EE) IDs is "EE_List"
4) In sheet "EE_List", IDs will be in column A (starting in row 2)

5) In sheet "EE_List", column B will contain the names, so ideally the worksheets can be a concatenation (like, in the sheet "EE_LIST", =concatenate($A2&"_"&$B2)

Here is the the solution provided in the old thread:
VBA Code:
Option Explicit

Sub CreateEmplSheets()
'JBeaucaire  (9/30/2009)
'Create individual copies of template for each EmpID
Dim LR As Long, ID As Range, Emp As Range
Application.ScreenUpdating = False
LR = Sheets("EE_List").Range("A" & Rows.Count).End(xlUp).Row

Set Emp = Sheets("EE_List").Range("A2:A" & LR)

    For Each ID In Emp
        Sheets("DB_Template").Range("A1") = ID
        Sheets("DB_Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = ID.Value & "_" & ID.Offset(0, 1).Value
    Next ID

Application.ScreenUpdating = True
End Sub[/COLOR]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,983
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top