Naming multiple sheets at the same time

[Amstrad]

I want to be able to name multiple sheets in a workbook at once, rather than having to create a new sheet and rename it manually. In Sheet1, in coulumn A I have a list of names that I want each new sheet to be named. I managed to find the following code, and I have installed it as a macro:

Sub copySheet2()
Dim rngName As Range
Dim i As Integer
Set rngName = ThisWorkbook.Sheets("Sheet1").Range("a1")
Do Until rngName.Value = ""
i = ThisWorkbook.Sheets.Count
Sheets("Sheet2").Copy After:=Sheets(i)
ThisWorkbook.Sheets(i + 1).Name = rngName.Value
Set rngName = rngName.Offset(1)
Loop
End Sub

When I run it, however I get the following error:

"Can't execute code in break mode."

When I debug, it highlights Sheets("Sheet2").Copy After:=Sheets(i)

Any ideas on what is needed to fix this?

I also have a folder that contains a number of txt files. The txt files contain the data that I want to import into the new sheets I have created. In the folder I have set the default program to open the txt files as Excel.

Rather than manually copying and pasting the data from the txt file into its corresponding sheet, how can I build a macro that will import all the data all in one go.

Your help is greatly appreciated

 

[VoG]

Try this

Sub CopySh2()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = .Range("A" & i).Value
    Next i
End With
End Sub

 

[Dryver14]

select the cells in A then run macro




Sub createmanysheets()
Dim ThisWS As Variant
For Each cell In Selection
ThisWS = cell.Value
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = ThisWS
Next cell
End Sub

 

[Amstrad]

select the cells in A then run macro

Sub createmanysheets()
Dim ThisWS As Variant
For Each cell In Selection
ThisWS = cell.Value
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = ThisWS
Next cell
End Sub

Works a treat. Thank you so much.

Now I've just got to try and figure out how to merge the data from the txt files I have into each corresponding sheet ....

Any ideas would be gratefully received