Named Ranges
 

I have been trying to solve a problem I am experiencing with named ranges but
to no avail. Any suggestions would be appreciated.

I have a workbook that has a number of named ranges which are referred to in
calculations and as graph series'.

I have incoporated an export function that allows the user to 'export' a
report but this is now creating some problems. Basically, the process I have
used in the past is to write some code that will take a copy of the necessary
worksheet, copy and pastespecial the values, rename the sheet, move it to a
new workbook and save the workbook to the desktop with a designated filename.
Ultimately I end up with two workbooks open, the new 'export' and also my
main workbook. This has previously worked fine.

My problem with this example is that originally when I exported the
worksheet it maintained a link to the main workbook through the graph and
named ranges. I thought I had counteracted this by incorporating the
following code:

Sub DeleteNames()
Dim objName As Excel.Name
For Each objName In ActiveWorkbook.Names
objName.Delete
Next objName
End Sub

This appears to work fine with the 'export' in that no links are reported.
However, the main workbook in the background begins to have issues with named
ranges. Not that these are deleted, but in the sense that the calculation
doesn't update. As the subject of the main workbook can be altered, the
values returned through the named ranges should alter accordingly, they
don't, unless I click into the formula bar and press Enter, and this appears
to 'kickstart' it again.

Any ideas? I had thought about closing the main workbook, but this would
need to be done prior to the deletion of the named ranges in the Export, and
this then leads to MS Excel encountering errors as I guess the rest of the
code was terminated.

Any help would be gratefully received. All the code is below.

Phil

Private Sub ExportReport()

Dim ExportSheet As String
Dim SheetNumber As Integer
Dim CurrentSchool As Integer

ExportSheet = Sheets("Schools").Range("J15").Value

If ExportSheet = "" Then GoTo BlankSheet

Application.ScreenUpdating = False

Sheets(ExportSheet).Select

'Count Number of Sheets
SheetNumber = ActiveWorkbook.Worksheets.Count

'Copy Profile
Sheets(ExportSheet).Copy After:=Sheets(SheetNumber)
ActiveSheet.Unprotect
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

CurrentSchool = Sheets("Schools").Range("J5").Value

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,
Format:=xlPicture
ActiveWindow.Visible = False
Selection.Delete
Range("C10").Select
ActiveSheet.Paste
Range("E2").Select

ActiveSheet.Move

Desktopsave

Exit Sub

BlankSheet:
MsgBox ("No report has been selected. Please select a report then press
the button")

End Sub

Sub Desktopsave()

Dim WSHShell As Object
Dim DesktopPath As String
Dim filename As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

filename = ActiveSheet.Name
Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")

ActiveWorkbook.SaveAs DesktopPath & "\" & filename & ".xls"

Set WSHShell = Nothing

Windows("Attendance vs FFT Attainment Probability 12.6.07.xls").Activate
Sheets("Opening Page").Select

Windows(filename & ".xls").Activate

'Delete Named Ranges in Copied File
DeleteNames
FormatSheet

Application.CutCopyMode = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

MsgBox ("This file has been saved to your Desktop")

End Sub

Sub DeleteNames()
Dim objName As Excel.Name
For Each objName In ActiveWorkbook.Names
objName.Delete
Next objName
End Sub

Named Ranges
 

Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

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

I bet between those two tools, you'll find the problem (and the solution).

Phil wrote:

I have been trying to solve a problem I am experiencing with named ranges but
to no avail. Any suggestions would be appreciated.

I have a workbook that has a number of named ranges which are referred to in
calculations and as graph series'.

I have incoporated an export function that allows the user to 'export' a
report but this is now creating some problems. Basically, the process I have
used in the past is to write some code that will take a copy of the necessary
worksheet, copy and pastespecial the values, rename the sheet, move it to a
new workbook and save the workbook to the desktop with a designated filename.
Ultimately I end up with two workbooks open, the new 'export' and also my
main workbook. This has previously worked fine.

My problem with this example is that originally when I exported the
worksheet it maintained a link to the main workbook through the graph and
named ranges. I thought I had counteracted this by incorporating the
following code:

Sub DeleteNames()
Dim objName As Excel.Name
For Each objName In ActiveWorkbook.Names
objName.Delete
Next objName
End Sub

This appears to work fine with the 'export' in that no links are reported.
However, the main workbook in the background begins to have issues with named
ranges. Not that these are deleted, but in the sense that the calculation
doesn't update. As the subject of the main workbook can be altered, the
values returned through the named ranges should alter accordingly, they
don't, unless I click into the formula bar and press Enter, and this appears
to 'kickstart' it again.

Any ideas? I had thought about closing the main workbook, but this would
need to be done prior to the deletion of the named ranges in the Export, and
this then leads to MS Excel encountering errors as I guess the rest of the
code was terminated.

Any help would be gratefully received. All the code is below.

Phil

Private Sub ExportReport()

Dim ExportSheet As String
Dim SheetNumber As Integer
Dim CurrentSchool As Integer

ExportSheet = Sheets("Schools").Range("J15").Value

If ExportSheet = "" Then GoTo BlankSheet

Application.ScreenUpdating = False

Sheets(ExportSheet).Select

'Count Number of Sheets
SheetNumber = ActiveWorkbook.Worksheets.Count

'Copy Profile
Sheets(ExportSheet).Copy After:=Sheets(SheetNumber)
ActiveSheet.Unprotect
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

CurrentSchool = Sheets("Schools").Range("J5").Value

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,
Format:=xlPicture
ActiveWindow.Visible = False
Selection.Delete
Range("C10").Select
ActiveSheet.Paste
Range("E2").Select

ActiveSheet.Move

Desktopsave

Exit Sub

BlankSheet:
MsgBox ("No report has been selected. Please select a report then press
the button")

End Sub

Sub Desktopsave()

Dim WSHShell As Object
Dim DesktopPath As String
Dim filename As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

filename = ActiveSheet.Name
Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")

ActiveWorkbook.SaveAs DesktopPath & "\" & filename & ".xls"

Set WSHShell = Nothing

Windows("Attendance vs FFT Attainment Probability 12.6.07.xls").Activate
Sheets("Opening Page").Select

Windows(filename & ".xls").Activate

'Delete Named Ranges in Copied File
DeleteNames
FormatSheet

Application.CutCopyMode = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

MsgBox ("This file has been saved to your Desktop")

End Sub

Sub DeleteNames()
Dim objName As Excel.Name
For Each objName In ActiveWorkbook.Names
objName.Delete
Next objName
End Sub

--

Dave Peterson