P
Pausert of Nikkeldepaiin
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
before:=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?
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
before:=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?