Using VBA to add data to existing VBA

[seanjon]

Howdy!


I have a workbook for my employee’s weekly performance. Iexport the data from another program, copy it to one sheet (“WEEKLY NC”) and copystuff to another sheet (“WEEKLY BA”). I then run a VBA that sorts through allthis data, picks each employee’s data from each sheet and places itstrategically on that employee’s personal sheet. I then print them out and wediscuss it.
What I would like to do is create a VBA that will add a newemployee to the existing VBAs that picks and pulls the information. Each weekof the month has its own VBA code, so adding a new member is a copy/paste/editpain in the you know what.
So, I was thinking I could create a userform that would popup with an “add employee” button which will create the employee’s sheet (I havethe tools to do this part) and then have it write that employee’s VBA code tothe existing strings I already have. I hope this makes sense.

1. Can this be done? Can you write a VBA that willinsert VBA into an existing VBA?
   
2. Does anyone know how to do it?

Here is an example
(Note: this is only week 1 stuff, and only 1 employee’scode. There are 5 different instances (one for each week) of this for eachemployee.)

Private Sub Week1NC_Click()
Application.ScreenUpdating = False
Call BOB_NC_1
Call BOB_BA_1
Sheets("MONTHLY TOTALS").Select
Application.ScreenUpdating = True
End Sub
Private Sub BOB_NC_1()
Dim shSource As Worksheet
Dim shDestination As Worksheet
Set shSource = Sheets("WEEKLY NC")
Set shDestination = Sheets("BOB")
shSource.UsedRange.AutoFilter Field:=13, Criteria1:="*BOB*"
shSource.UsedRange.Columns("G:K").Offset(1).Copy
shDestination.Range("AA" & Rows.Count).End(xlUp).Offset(1).PasteSpecialxlPasteValues
Application.CutCopyMode = False
shSource.AutoFilterMode = False
Application.Goto shDestination.Range("A1")
End Sub
Private Sub BOB_BA_1()
Dim shSource As Worksheet
Dim shDestination As Worksheet
Set shSource = Sheets("WEEKLY BA")
Set shDestination = Sheets("BOB")
shSource.UsedRange.AutoFilter Field:=2, Criteria1:="*BOB*"
shSource.UsedRange.Columns("C:H").Offset(1).Copy
shDestination.Range("AH" &Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
shSource.AutoFilterMode = False
Application.Goto shDestination.Range("A1")
End Sub

I want to copy this VBA, paste it in the correct place, andchange the name to the new employee’s name.
Thanks!

 

[Jon Peltier]

It seems to me that it would be much easier to just use the same VBA, but naybe put the employee's name somewhere, maybe cell A1 on the active sheet.

Your subs would just be named EMPLOYEE_NC_1 and EMPLOYEE_BA_1.

Your search Criteria1 would be "*" & ActiveSheet.Range("A1").Value & "*"

This way you don't have to hard code the employee name in the VBA.

 

[seanjon]

Jon,

That’s a great suggestion. However, the placement of theemployee names is never the same week-to-week so doing a search based on thatcriteria isn’t possible (unless I am missing what you are suggesting).
Again, I appreciate your thoughts, but I don’t want to getoff topic. I am still wondering if it is possible to edit/add VBA code with VBAcode.

Sean

 

[Jon Peltier]

Short answer, it is possible to use VBA to edit VBA code, but how is the editing code gong to find the employee names to edit itself, if it can't find them to use directly in a search?

Longer answer, sure it's possible, but that way is filled with anguish and despair.

Another question, what is the workflow: how is the code run? Could the user select the cell with the employee name and then start the code, and let the code use the active cell's value? Or could the code use an InputBox to ask the user for the name? There have to be a dozen better ways than writing a procedure to rewrite code every time.