Quick question about dynamically adding and naming worksheets

R

Robbyn

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
 
T

Tom Ogilvy

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

--
Regards,
Tom Ogilvy


Robbyn said:
The following snippet of code works, but I'd like to simplify it. In the
Click to expand...
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,""
Click to expand...
"",INDIRECT""1a!""&ROW ()+11))"
 
R

Robbyn

Thanks again Tom!

Tom Ogilvy said:
"=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))"
Click to expand...
 
Top