copy sheet1 and name sheets using names from a range

[DL]

I want to modify this macro so that it copies sheet1, the appropriate number
of times.
At present it adds new blank sheets.

Sub CopySheet1()
Dim arr As Variant
arr = Range("i1:i44").Value
For i = LBound(arr) To UBound(arr)
Set NewSheet = Sheets.addd
NewSheet.Name = arr(i, 1)
Next i

End Sub

 

[Jan Karel Pieterse]

Hi,

Sub CopySheet1()
Dim arr As Variant
Dim newsheet As Worksheet
arr = Range("i1:i44").Value
For i = LBound(arr) To UBound(arr)
Worksheets("Sheet1").Copy after:=Worksheets("Sheet1")
Set newsheet = ActiveSheet
NewSheet.Name = arr(i, 1)
Next i
End Sub

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[DL]

Hi Jan,

Thank you for your help.

That works wonderfully well.