Create and name multiple sheets
I'm using the following macro to take the value in cell B2 and save each
worksheet and the worksheet called "Schedule" into their own workbooks. It
keeps hanging at the line that reads ".value = wks.name
Any ideas how to fix this?
Mike
Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
Worksheets(Array("Schedule", wks.Name)).Copy
With ActiveSheet
..Parent.Worksheets("Schedule").Move _
befo=.Parent.Worksheets(1)
With .Parent.Worksheets(wks.Name).Range("B2")
..NumberFormat = "text"
..Value = wks.Name
End With
..Parent.SaveAs Filename:="\\bdfiler\masterads\2007 Master Ad" & wks.Name &
".xls", _
FileFormat:=xlWorkbookNormal
..Parent.Close savechanges:=False
End With
Next wks
End Sub
"Gord Dibben" wrote:
See Dave's post for making new books from sheets.
Gord
On Wed, 28 Feb 2007 11:48:13 -0800, MikeD1224
wrote:
Gord,
Do you also know how I can save each worksheet as a workbook with the name
of the file being derived from the worksheet name?
"Gord Dibben" wrote:
Mike
Sheet1 will contain your list of names.
Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: 1-1-2007
' and name the sheets accordingly
Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range
Set TemplateWks = Worksheets("1-1-2007")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell
End Sub
Gord Dibben MS Excel MVP
On Tue, 27 Feb 2007 15:51:08 -0800, MikeD1224
wrote:
I currently have a worksheet named 1-1-2007. I need to copy this sheet 60
times and rename each. The names for each sheet are contained on Sheet1
(named "Schedule") and are in column C.
Can I use a macro or an array formula to do this? Any help would be great.
Thanks,
Mike
|