how to generate and add code with code

[supper's ready]

hello,

i have the following code:

Sub MakeSheets()

Dim ListofNames As Range
Dim c As Range

Set ListofNames = ActiveSheet.[B3:B17]

For Each c In ListofNames
If c.Text <> vbNullString Then
On Error GoTo InvalidName
ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
NextC:
End If
Next

Exit Sub

InvalidName:
MsgBox """" & c.Value & """ is an invalid sheet name.", vbOKOnly + vbCritical, "Add Sheets"
Resume NextC

End Sub

i want to add code that will add an if statement to a sub called Sub CreateWorkbooks() (in module 1) for each instance of the loop.

The if statement will take a column address from C3:C17 (where c3 corresponds to b3 instance etc) to define a variable.
heres what the if statement looks like:

If Sheet.Name = "value from b3 Then
Columns("B:B").Select 'B:B comes from value in C3
Selection.NumberFormat = "m/d/yyyy"
dtMin = WorksheetFunction.Min(Columns("B:B"))
dtMax = WorksheetFunction.Max(Columns("B:B"))
DayMin = Day(dtMin)
DayMax = Day(dtMax)
End If

please please help! thanks!

 

[Weaver]

I'm looking at this and wondering if I've missed something.

Why would you want to hard code it this way? Surely you can pull the relevant info from the cell on the fly and work with it at run time?

 

[supper's ready]

the if statement will go to another macros which will be run at a different time. and the data in that worksheet will be entered in the future and will change. also the variables to be assigned get changed because its all part of a loop

 

[Weaver]

Couldn't you just store the relevant variables somewhere, say in a hidden sheet and then use them later.

It's possible to write a macro with a macro, see

http://www.cpearson.com/excel/vbe.aspx

but it's not something I'd ever do.

 

[supper's ready]

yeah i was thinking about it yesterday and realized that is the best way to do it.

i just need to figure out how to write an if statement like:

if sheet.name in some range then

what's the code for that lol? once i figure that out, i got the rest thanks for the help.

 

[Weaver]

If it's just 1 cell

if sheet.name=range("B3").value then

or maybe if it's a range

if worksheetfunction.countif(range("B3:B20"),sheet.name) > 0 then