Losing Named Range Definitions

[bombardier]

I'm trying to copy the contents of a named range on one worksheet over
to another named range on another worksheet, using Paste Special macro
code. However, when I execute the macro, it seems to lose the
definition of the target named range.

1. I defined the source named range "RisksTable" as $AI$31:$AR$36 on
one worksheet, called "6-Blocker", using the spreadsheet menu
functions.
2. I defined the target named range "RisksTableTarget as $A$33:$J38 on
another worksheet, called "Project Risks", also using the spreadsheet
menu functions.
3. My code looks like this:

Sheets("6-Blocker").Select
Range("RisksTable").Select
Selection.Copy
Sheets("Project Risks").Select
Range("RisksTableTarget").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

4. When I execute the code, it fails with run-time error 1004, "Method
'Range' of object_Global failure". I went back to the range
definitions and found that while RisksTable was intact, the range
RisksTableTarget has been re-defined somehow as 'Project Risks'!REF#.

5. If instead of referencing the named range, "RisksTableTarget", I
use a cell range, i.e., $A$33:$J$38, the code runs fine. But I need to
reference the named range, because the user will be making changes to
named range definitions only, and not have to diddle around with the
code.

Can somebody help with this problem?

Thanks in advance.

 

[Guest]

I have not looked too close but you can give this a try... It will fail if
the two ranges are of different sizes (but so will yours)...

Sheets("Project Risks").Range("RisksTableTarget").Value = _
Sheets("6-Blocker").Range("RisksTable").Value

It makes the values in one range = to the values in the other range...

 

[Ken]

Your code worked fine for me in Excel 2003 SP2. The ranges did not
even need to be the same size and they could include merged cells;
which were two things that I thought could cause problems. What
version of Excel are you using?

Ken

 

[Peter T]

the range

RisksTableTarget has been re-defined somehow as 'Project Risks'!REF#.

The name "RisksTableTarget" is now corrupt and should not (cannot) be used
for any further purpose until corrected by redefining it, eg

dim rng as range
set rng = nothing
On error resume next
set rng = Range("RisksTableTarget")
On error got 0
If rng is nothing then
'hmm, what other potential problems lay in wait
ActiveWorkbook.Names.Add "RisksTableTarget", _
ActiveWorkbook.Worksheets("Project Risks").Range("$A$33:$J38")
End if

This most likely occurred due to the entire range's rows or columns having
been deleted. If only some row/columns were deleted/inserted the name would
still be valid though might refer to a different size to that you expect.
Might be a problem as it your code requires both ranges to be the same size.

Regards,
Peter T

 

[Don Guillett]

since both are the same size try
Sheets("Project Risks").Range("RisksTableTarget").value=_
Sheets("6-Blocker").Range("RisksTable").value

for not same size try
'UNtested
Sheets("6-Blocker").Range("RisksTable").Copy
Sheets("Project Risks").Range("RisksTableTarget").cells(1,1).PasteSpecial
Paste:=xlValues

 

[Bill Renaud]

I would use code like the following to work with named ranges. Set object
variables to the named range first with an error handler, then copy and
paste special. This makes it easier to single-step through the code. Notice
that I used rngRisksTableTarget.Cells(1, 1) with the PasteSpecial method to
prevent problems with different size ranges due to a user deleting some of
the rows or columns in the destination range. If the size of the
destination range absolutely has to match the size of the source range,
then an If statement comparing both the row and column counts of each range
should precede the copy operation.

Sub CopyData()
Dim rngRisksTable As Range
Dim rngRisksTableTarget As Range

With ThisWorkbook
On Error GoTo ErrNoRisksTable
Set rngRisksTable = .Names("RisksTable").RefersToRange

On Error GoTo ErrNoRisksTableTarget
Set rngRisksTableTarget = .Names("RisksTableTarget").RefersToRange
End With

rngRisksTable.Copy
rngRisksTableTarget.Cells(1, 1).PasteSpecial _
Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
GoTo ExitSub

ErrNoRisksTable:
MsgBox "'RisksTable' does not exist." & vbNewLine & _
"Cannot copy data.", vbCritical + vbOKOnly
GoTo ExitSub

ErrNoRisksTableTarget:
MsgBox "'RisksTableTarget' does not exist." & vbNewLine & _
"Cannot copy data.", vbCritical + vbOKOnly
GoTo ExitSub

ExitSub:
End Sub

 

[bombardier]

Don's code works, and I'm sure some of the other suggestions work.
Thanks everyone. But I have another issue that further complicate
this problem.

In the same Project Risks worksheet, I have a chart, which is pasted
from the main 6-Blocker worksheet. After the user examines the chart
and other data on the Project Risks worksheet, he or she can click a
button that deletes all the information, including the chart, and
reverts back to the main 6-Blocker worksheet. The deletion occurs so
that the information on the Project Risks worksheet can be dynamically
re-generated with perhaps new information from the main 6-Blocker
worksheet. The deletion of data seems to work with Selection.Delete
step. However the chart does not get deleted. The only way to delete
the chart seems to be either:
1) Delete the rows for all the data and chart. But when I do that,
the named range definitions seem to disappear;
2) Or delete the chart by selecting it and deleting or clearing. But
the problem with that is the chart renames itself automatically with a
name that increments, e.g. "Chart228", then "Chart229". I can't seem
to capture that renamed chart in a macro. Is there a way to
permanently name a chart with static reference? I think that would
solve this problem. Thanks again for all your help.

 

[ward376]

You don't have to refer to the chart by name to delete it:

ActiveSheet.ChartObjects(1).Delete

Cliff Edwards

 

[ward376]

This will delete all the charts on a sheet:

Sub delChart()
Dim x As Integer
For x = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(x).Delete
Next x
End Sub

Cliff Edwards

 

[Peter T]

Hi Cliff,

When deleting items which are effectively in a collection, best to loop from
the last to the first. Why - say start with 3 items, delete two of them, now
cannot delete the 3rd item as now there's only one left (with loop from 1 to
3).

For x = ActiveSheet.ChartObjects.Count To 1 Step -1
ActiveSheet.ChartObjects(x).Delete
Next x

However to delete all chartobjects can do simply -
ActiveSheet.ChartObjects.Delete

Regards,
Peter T

 

[bobkaku]

This will delete all the charts on a sheet:

Sub delChart()
Dim x As Integer
For x = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(x).Delete
Next x
End Sub

Cliff Edwards

Cliff, thanks for your help. There was a slight change I had to make
to the code above to make it ChartObjects property recognized. In
place of your For statement, I replaced it with:

For i = Application.Worksheets("mySheet").ChartObjects.Count To 1
Step -1

Anyway, all my problems are solved. That is, until the user starts to
request other changes. Blessings to you all.