Create new sheet based on instances of variables

[RP1America]

For each UserID and PlanNumber combination, I would like to create a new worksheet and enter data into that worksheet (template).

Column C Column D Column E
UserID PlanNumber LotNumber
123456789 G11111 AB
123456789 G11111 X6
123456789 G11111 31
987654321 G11111 12
987654321 G11111 A3
123456789 G22222 G7
123456789 G22222 22

Given the example above, I would expect 3 separate spreadsheets to be created for each UserID/PlanNumber combination. I would also need to be able to reference and list the LotNumbers within the designated new sheet.

Any ideas of how to accomplish this?

I hope I am explaining it in a manner that is easy to comprehend my need. If not, just let me know and I will try to explain.

Thanks!

 

[SkipVought]

hi,

Spawning sheets/tables/charts is usually not a good idea, as it makes the maintenance and analysis of the workbook more difficult.

What is the reason that you could not use AutoFilter criteria to display a specific UserID/PlanNumber on one sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

I am attempting to automate this as much as possible. If I used an autofilter, wouldn't I need the user to input a UserID/PlanNumber combination?

 

[SkipVought]

So is the UserID the Network ID, that you could get via Environ("username") function?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

No, UserID is a list of client Social Security Numbers.

 

[SkipVought]

So how will the sheets be named?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Dim strNewSheetName As String
strNewSheetName = 'Will need to be able to value by PlanNumber here
   
    Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = strNewSheetName

 

[SkipVought]

strNewSheetName = 'Will need to be able to value by PlanNumber here

Better think this out!

You have TWO separate ID/Plan for ONE PlanNumber in your example...
[tt]
123456789 G11111
987654321 G11111
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}