I stole and then modified the following code. Connected to a button on a
worksheet in my current workbook, it creates a new workbook containing only
one sheet, which is a copy of the old one. (Just before the end, it also
turns off the sheet tabs in the new workbook.):

Sub CopyCoverRequestSheet()
'declare the variables
Dim wb As Workbook
Dim ws As Worksheet
Dim fName
With Application
'prevent unnecessary error messages
..DisplayAlerts = False
'switch off screen updating to speed up code and prevent screen flickering
..ScreenUpdating = False
Set wb = Workbooks.Add
Do
'use inbuilt dialog to prompt for a name, this will only store the name to
be used later
fName = Application.GetSaveAsFilename
'continue until user enters a valid name
Loop Until fName < False
'save the new workbook
ActiveWorkbook.SaveAs Filename:=fName
'copy sheet to new workbook, you must change the sheet to be copied to match
yours
ThisWorkbook.Worksheets("3. Submit Cover & Title Page MS").Copy
befo=Worksheets("Sheet1")
'remove empty sheets
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < ActiveSheet.Name Then ws.Delete
Next ws
'return application to normal
ActiveWindow.DisplayWorkbookTabs = False
..DisplayAlerts = True
..ScreenUpdating = True
End With
'clear memory
Set wb = Nothing
Set ws = Nothing
End Sub

This works really well. My problem, though, is that the new workbook
contains links to the old one. Is there a way to modify this code to sever
links of all types to the old workbook? I've doodled around with the
BreakLinks method, but I keep getting runtime errors. Can anyone help?

If that sheet that is copied has formulas that refer to other sheets in the
original workbook, you'll end up with links back to that original workbook.

You could convert those formulas (all formulas???) to values.

Maybe just selecting all the cells, edit|copy, edit|paste special values would
be enough???

In xl2003, you can use:
Edit|Links and break the links (converting them to values)

Record a macro when you do either and you'll have the code you need.

Pausert of Nikkeldepaiin wrote:

I stole and then modified the following code. Connected to a button on a
worksheet in my current workbook, it creates a new workbook containing only
one sheet, which is a copy of the old one. (Just before the end, it also
turns off the sheet tabs in the new workbook.):

Sub CopyCoverRequestSheet()
'declare the variables
Dim wb As Workbook
Dim ws As Worksheet
Dim fName
With Application
'prevent unnecessary error messages
.DisplayAlerts = False
'switch off screen updating to speed up code and prevent screen flickering
.ScreenUpdating = False
Set wb = Workbooks.Add
Do
'use inbuilt dialog to prompt for a name, this will only store the name to
be used later
fName = Application.GetSaveAsFilename
'continue until user enters a valid name
Loop Until fName < False
'save the new workbook
ActiveWorkbook.SaveAs Filename:=fName
'copy sheet to new workbook, you must change the sheet to be copied to match
yours
ThisWorkbook.Worksheets("3. Submit Cover & Title Page MS").Copy
befo=Worksheets("Sheet1")
'remove empty sheets
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < ActiveSheet.Name Then ws.Delete
Next ws
'return application to normal
ActiveWindow.DisplayWorkbookTabs = False
.DisplayAlerts = True
.ScreenUpdating = True
End With
'clear memory
Set wb = Nothing
Set ws = Nothing
End Sub

This works really well. My problem, though, is that the new workbook
contains links to the old one. Is there a way to modify this code to sever
links of all types to the old workbook? I've doodled around with the
BreakLinks method, but I keep getting runtime errors. Can anyone help?

--

Dave Peterson

Thanks for the idea, Dave! I think I'm still stuck, though. As I've been
trying to think this through since my original post, I think the links are
coming from names that I'm inadvertently picking up from the original
workbook, which has a bazillion named cells in it. Do you know if there a
way, in the course of running the copy code, to wipe out all names in the new
workbook?

I must admit to a minor panic--I'm supposed to have something to show at
9:30, and although the links thing probably won't be an issue for the rest of
the team, it's going to bug me a LOT. If you have any more ideas, I'd
appreciate it.

"Dave Peterson" wrote:

If that sheet that is copied has formulas that refer to other sheets in the
original workbook, you'll end up with links back to that original workbook.

You could convert those formulas (all formulas???) to values.

Maybe just selecting all the cells, edit|copy, edit|paste special values would
be enough???

In xl2003, you can use:
Edit|Links and break the links (converting them to values)

Record a macro when you do either and you'll have the code you need.

Pausert of Nikkeldepaiin wrote:

I stole and then modified the following code. Connected to a button on a
worksheet in my current workbook, it creates a new workbook containing only
one sheet, which is a copy of the old one. (Just before the end, it also
turns off the sheet tabs in the new workbook.):

Sub CopyCoverRequestSheet()
'declare the variables
Dim wb As Workbook
Dim ws As Worksheet
Dim fName
With Application
'prevent unnecessary error messages
.DisplayAlerts = False
'switch off screen updating to speed up code and prevent screen flickering
.ScreenUpdating = False
Set wb = Workbooks.Add
Do
'use inbuilt dialog to prompt for a name, this will only store the name to
be used later
fName = Application.GetSaveAsFilename
'continue until user enters a valid name
Loop Until fName < False
'save the new workbook
ActiveWorkbook.SaveAs Filename:=fName
'copy sheet to new workbook, you must change the sheet to be copied to match
yours
ThisWorkbook.Worksheets("3. Submit Cover & Title Page MS").Copy
befo=Worksheets("Sheet1")
'remove empty sheets
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < ActiveSheet.Name Then ws.Delete
Next ws
'return application to normal
ActiveWindow.DisplayWorkbookTabs = False
.DisplayAlerts = True
.ScreenUpdating = True
End With
'clear memory
Set wb = Nothing
Set ws = Nothing
End Sub

This works really well. My problem, though, is that the new workbook
contains links to the old one. Is there a way to modify this code to sever
links of all types to the old workbook? I've doodled around with the
BreakLinks method, but I keep getting runtime errors. Can anyone help?

--

Dave Peterson

Here are some tools that'll help you:

Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

And I like Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

=====
There are names that excel uses that you may not want to delete.

I think I'd use the name manager to find the names that get copied over. Then
make a note of them and delete them explicitly.

On error resume next 'in case a future version doesn't have the same names
with yournewworkbook
.names("name1").delete
.names("name2").delete
End with
On error goto 0

But if you find that you can delete all the names without breaking stuff:

Dim myName as name
for each myName in yournewworkbook.names
myName.delete
next myname

But I'd use that NameManager before I did anything.


Pausert of Nikkeldepaiin wrote:

Thanks for the idea, Dave! I think I'm still stuck, though. As I've been
trying to think this through since my original post, I think the links are
coming from names that I'm inadvertently picking up from the original
workbook, which has a bazillion named cells in it. Do you know if there a
way, in the course of running the copy code, to wipe out all names in the new
workbook?

I must admit to a minor panic--I'm supposed to have something to show at
9:30, and although the links thing probably won't be an issue for the rest of
the team, it's going to bug me a LOT. If you have any more ideas, I'd
appreciate it.

"Dave Peterson" wrote:

If that sheet that is copied has formulas that refer to other sheets in the
original workbook, you'll end up with links back to that original workbook.

You could convert those formulas (all formulas???) to values.

Maybe just selecting all the cells, edit|copy, edit|paste special values would
be enough???

In xl2003, you can use:
Edit|Links and break the links (converting them to values)

Record a macro when you do either and you'll have the code you need.

Pausert of Nikkeldepaiin wrote:

I stole and then modified the following code. Connected to a button on a
worksheet in my current workbook, it creates a new workbook containing only
one sheet, which is a copy of the old one. (Just before the end, it also
turns off the sheet tabs in the new workbook.):

Sub CopyCoverRequestSheet()
'declare the variables
Dim wb As Workbook
Dim ws As Worksheet
Dim fName
With Application
'prevent unnecessary error messages
.DisplayAlerts = False
'switch off screen updating to speed up code and prevent screen flickering
.ScreenUpdating = False
Set wb = Workbooks.Add
Do
'use inbuilt dialog to prompt for a name, this will only store the name to
be used later
fName = Application.GetSaveAsFilename
'continue until user enters a valid name
Loop Until fName < False
'save the new workbook
ActiveWorkbook.SaveAs Filename:=fName
'copy sheet to new workbook, you must change the sheet to be copied to match
yours
ThisWorkbook.Worksheets("3. Submit Cover & Title Page MS").Copy
befo=Worksheets("Sheet1")
'remove empty sheets
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < ActiveSheet.Name Then ws.Delete
Next ws
'return application to normal
ActiveWindow.DisplayWorkbookTabs = False
.DisplayAlerts = True
.ScreenUpdating = True
End With
'clear memory
Set wb = Nothing
Set ws = Nothing
End Sub

This works really well. My problem, though, is that the new workbook
contains links to the old one. Is there a way to modify this code to sever
links of all types to the old workbook? I've doodled around with the
BreakLinks method, but I keep getting runtime errors. Can anyone help?

--

Dave Peterson


--

Dave Peterson