Is that worksheet that you're copying protected (with B2 locked)?



MikeD1224 wrote:

The error message is

"Run-Time Error 1004: unable to set the NumberFormat Property of the Range
Class. The cell is formatted in the following format: 14-Mar-07.

"Dave Peterson" wrote:

And if that doesn't help, what is the error message that you get?

Dave Peterson wrote:

First, change this line:
.NumberFormat = "text"
to
.NumberFormat = "@"

@ means that the cell should be formatted as text.

Is that worksheet that you're copying protected (with B2 locked)?

MikeD1224 wrote:

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





--

Dave Peterson

--

Dave Peterson


--

Dave Peterson