If I have a base worksheet template (which can change), I need to create sheets identical to the template for a specific list.
Each sheet will be named according to the list and a cell in each of the sheets to be named according to the list also.
The attached file is what I am looking for. For example, sheets will be generated (based on "BASE" template) and named according to column "D" and each sheet will have a named cell according to column "E".
When finished, the list would need to be deleted and just the sheets remaining.
Sub CreateSheets() Dim tmpSheet As Worksheet, lstSheet As Worksheet Dim rw As Long Set tmpSheet = Worksheets("BASE") Set lstSheet = Worksheets("LIST") For rw = 4 To lstSheet.Cells(Rows.Count, "D").End(xlUp).Row tmpSheet.Copy After:=Worksheets(Worksheets.Count) With ActiveSheet .Name = lstSheet.Cells(rw, "D") .Range("E1").Value = lstSheet.Cells(rw, "E") End With Next Application.DisplayAlerts = False lstSheet.Delete Application.DisplayAlerts = TrueEnd Sub
If the sheets are required to have a different name, is there a way to rename the sheets that have been created with another name based on another column say "E".
For example, London No1, London No2... need to be renamed to Lon No1, Lon No2 etc..
This may occur after populating the created sheets with data?
forever7
ASKER
should have said ...another column say "F".
Wayne Taylor (webtubbs)
You can use the below macro to rename the sheets based on the values in column "F" on the LIST sheet...
Sub RenameSheets() Dim lstSheet As Worksheet, ws As Worksheet, rw As Long Set lstSheet = Worksheets("LIST") For rw = 4 To lstSheet.Cells(Rows.Count, "D").End(xlUp).Row On Error Resume Next Set ws = Worksheets(lstSheet.Cells(rw, "D").Value) If Not ws Is Nothing Then ws.Name = lstSheet.Cells(rw, "F") End If On Error GoTo 0 NextEnd Sub
The rename works, but if I want to rename again to something else, is there a way to rename a sheet to another sheet based on the list.
for example, I tried renaming again by but it renamed in different order (it renamed the LIST and BASE tabs)?
forever7
ASKER
I may need to generate around 30-40 sheets. This a great help but if I can get the renaming correct then I can generate sheets, rename them if asked. and possibly rename again if things change.
Open in new window