Quick question about dynamically adding and naming worksheets

[Guest]

The following snippet of code works, but I'd like to simplify it. In the formula, I'd like to change the sheet name to ctl.Caption, but no clue on the syntax of it. I've tried a million different things, so I'm starting to wonder if it's even possible. Thanks for any help you can give.(again)

For each ctl in grpClasses.Controls
If ctl.Value = True Then
Unload Me
'On Error GoTo e:
Set wsTest = Worksheets.Add
wsTest.Name = "Report" & ctl.caption
With Worksheets(ctl.Caption)
Range("a4").Select
ActiveCell.FormulaR1C1 = _
"=IF(INDIRECT(""1a!A""&ROW()+11)=0,"" "",INDIRECT""1a!""&ROW ()+11))"
Selection.AutoFill Destination:=Range("A4:A28"), Type:=xlFillDefault
End With

Robbyn

 

[Tom Ogilvy]

"=IF(INDIRECT(""" & ctl.Caption & _
"!A""&ROW()+11)=0,"" "",INDIRECT(""" & ctrl.Caption & _
"!A""&ROW()+11))"

--
Regards,
Tom Ogilvy

The following snippet of code works, but I'd like to simplify it. In the

formula, I'd like to change the sheet name to ctl.Caption, but no clue on
the syntax of it. I've tried a million different things, so I'm starting to
wonder if it's even possible. Thanks for any help you can give.(again)

For each ctl in grpClasses.Controls
If ctl.Value = True Then
Unload Me
'On Error GoTo e:
Set wsTest = Worksheets.Add
wsTest.Name = "Report" & ctl.caption
With Worksheets(ctl.Caption)
Range("a4").Select
ActiveCell.FormulaR1C1 = _
"=IF(INDIRECT(""1a!A""&ROW()+11)=0,""

"",INDIRECT""1a!""&ROW ()+11))"

 

[Guest]

Thanks again Tom!

"=IF(INDIRECT(""" & ctl.Caption & _
"!A""&ROW()+11)=0,"" "",INDIRECT(""" & ctrl.Caption & _
"!A""&ROW()+11))"

--
Regards,
Tom Ogilvy



formula, I'd like to change the sheet name to ctl.Caption, but no clue on
the syntax of it. I've tried a million different things, so I'm starting to
wonder if it's even possible. Thanks for any help you can give.(again)
"",INDIRECT""1a!""&ROW ()+11))"