creating new sheets based on dynamic list

[ysherriff]

to al,

how are you? I have a macro that creates and renames sheets based on dynamic list. the list range changes based on offset function.

The issue is the macro wants to keep creating blank sheets and rename it. I get an error message because when they create the new sheet, there is not a name associated with it. How can I effectively tell this macro to stop creating new sheets once it reach the end of the list. the text highlighted in red is where the error lies.

 For i = 1 To filecount
 
  Range("PTL_NAME_RNG").Select
  Selection.ClearContents
 
  Set MyRange = Sheets("Control").Range("PTL_NAME_START").Offset(i, 0)
  Set MyRange = Range(MyRange, MyRange.End(xlDown))
 


For Each MyCell In MyRange
  Sheets("Template").Copy After:=Sheets(Sheets.Count) 'creates a new worksheet
  Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
  Range("PTL_NAME") = MyCell.Value
  ActiveSheet.Protect Password:="ops", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
  Next MyCell]

I have uploaded the template file and the generator file.

Thanks for your help.

 

[ysherriff]

nevermind. I found the answer....... thanks

 

[ysherriff]

nevermind. still need help

 

[Marc L]

Hi,

try with CurrentRegion property (see in VBA inner help) …
​

 

[ysherriff]

i apologize. I do not understand

 

[ysherriff]

ok. found the result. create an if statement to exit loop if blank is found.

Thanks