I think so.

But I'm not sure what.

BlankProduction.xls is a workbook. You said you wanted to copy a sheet--what's
the name of the sheet? (And it's in blankproduction.xls, right?)

And where does the macro live? In DateList.xls?

This line assumes that the list lives in the workbook that contains the macro
and the sheet that holds the list is named Sheet2.
Set ListWks = ThisWorkbook.Worksheets("Sheet2")

If possible, I think I'd put the worksheet to be copied into the same workbook
that holds the macro. Sheet1 would hold the list of dates (the code needs to be
changed). And the sheet to be copied to the activeworkbook multiple times would
be called Template (or whatever you want).

This would work the way I'd want (not sure if it fits your requirements):

Option Explicit
Sub CreateSheets()

Dim myRng As Range
Dim myCell As Range
Dim ListWks As Worksheet
Dim TemplateWks As Worksheet

If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Please activate the workbook to create the worksheets"
Exit Sub
End If

Set TemplateWks = ThisWorkbook.Worksheets("Template")

Set ListWks = ThisWorkbook.Worksheets("Sheet1")
With ListWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With ActiveWorkbook
For Each myCell In myRng.Cells
TemplateWks.Copy _
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


KDP wrote:

ok,

The sheet that is to be copied is a file called BlankProduction.xls and has
no labels on the sheets (just sheet1, sheet2, sheet3.) the date list is in a
workbook called DateList.xls and the dates are listed, starting in cell A2.
It is also on "sheet1".

Do i need to change anything?

------------------------------------------------
"Dave Peterson" wrote:

Are you sure you're pointing to the correct list?

And where is the original sheet to be copied? Is it in the workbook with the
macro or in the workbook that gets the work done?

And what is the name of that sheet?



KDP wrote:

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


--

Dave Peterson


--

Dave Peterson