Create multiple worksheets from list
Dave,
It is creating the sheets but they are named in sequential order (sheet1,
sheet2, sheet3, etc). It's like it is not pulling from the list I created,
and they are formatted in 'dd-mmm' if that matters.
Also, can it copy the original sheet and paste it into the new sheets?
----------------------------------------------
"Dave Peterson" wrote:
One way:
Option Explicit
Sub CreateSheets()
Dim myRng As Range
Dim myCell As Range
Dim ListWks As Worksheet
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the workbook to create the worksheets"
Exit Sub
End If
Set ListWks = ThisWorkbook.Worksheets("Sheet2")
With ListWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With
With ActiveWorkbook
For Each myCell In myRng.Cells
.Worksheets.Add _
after:=.Sheets(.Sheets.Count)
On Error Resume Next
'watch out for /'s in the sheet name!
ActiveSheet.Name = myCell.Text 'format(mycell.value,"yyyy_mm_dd")
If Err.Number < 0 Then
MsgBox "Error renaming: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell
End With
End Sub
If you weren't skipping any dates, you could actually just build it into the
code.
KDP wrote:
i have a generic workbook that i create every month with sheets at the bottom
of the days of the month.
Is there anyway that i could create a macro to automatically create the
worksheets from a list of the dates needed?
--
Dave Peterson
|