VBA to copy a particular sheet to multiple sheets in a new workbook that are renamed from a range?

[aashish83]

I need to have FSR Level A sheet copied to new sheets created in macro below highlighted in Red. I have deadline and need help.




Sub FSRA()

With ActiveWorkbook.Sheets(Array("FSR Level A", "BI", "Instructions"))
.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "Audit Form FSR A", FileFormat:=52

End With

Dim sheets_count As Integer
Dim sheet_name As String
'Dim sheet_number As Integer
Dim i As Integer

sheet_count = Sheets("BI").Range("A17").Value
'sheet_number = Sheets("BI").Range("A17").value
For i = 1 To sheet_count
'sheet_name = "Connector " & sheet_number
sheet_name = Sheets("BI").Range("A18:A7000").Cells(i, 1).Value
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sheet_name

Next i
End Sub