Macro error

G

Guest

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 _
before:=.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
 
D

Dave Peterson

See your previous thread.
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 _
before:=.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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top