I don't know what this means:
And thanks for pointing out the " " (a named range does not need them)

But it sounds like you got the code running.

BeSmart wrote:

Thanks Dave

You explained that alot better than me...

Your "testing" code worked great - once I worked out 'where' to put it
within the code...
And thanks for pointing out the " " (a named range does not need them)

I'm also going to setup the named range across all worksheets to avoid the
error happening on existing sheets.
The error message will be good once Users start playing with it and adding
sheets that they shouldn't be adding...

--
Thank for your teachings & help
BeSmart

"Dave Peterson" wrote:

That's not true.

You can have a name that is local that is the same.

Sheet1!Name1 could refer to A1 on Sheet1
'sheet 99'!Name1 could refer to c3:d5,x9:z10,u5 on Sheet 99

When you define the name (manually), you can include the sheetname to make it
local (or a sheet level) name.

If you don't include the sheet name in the name, then the name will be global
(or workbook level).

In code, you can create local/sheet level names several ways. Here's a couple:

with worksheets("Sheet 99")
.range("a1:C9").name = "'" & .name & "'!Name1"
End with

Or
with worksheets("Sheet 99")
.names.add Name:="Name1", RefersTo:="=$a$1"
end with

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

Allows you to convert from local to global and global to local very easily.


joel wrote:

I never tried to have the same named range on my than one sheet. Excel
only allows one Named Range with the same name. You must have the named
range set to one of the sheets. So you need to remove the sheet
reference of the Named Range. Using the Address Property will do that
like in the code below. The frist two lines of the "TO" is common and
can be moved to the beginning of the macro.

From
sh.Range("GRPResults").Copy

To
Set MyRange = Range("GRPResults")
MyRangeAddr = MyRange.Address(external:=False)
sh.Range(MyRangeAddr).Copy

VBA Code:
--------------------



Sub CopyGRPSections()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim LastRowDest As Long
Dim NewRowDest As Long
Dim LastRowSource As Long
Dim DestLoc As Range


Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("GRP Data Collection").Cells.Clear


Set DestSh = ActiveWorkbook.Worksheets("GRP Data Collection")

Set MyRange = Range("GRPResults")
MyRangeAddr = MyRange.Address(external:=False)


For Each sh In ActiveWorkbook.Worksheets
If sh.Name < "Overview" And sh.Name < DestSh.Name And sh.Visible = True Then


If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
LastRowDest = 1
Set DestLoc = DestSh.Range("A1")
Else
LastRowDest = DestSh.Range("A" & Rows.Count).End(xlUp).Row
NewRowDest = LastRowDest + 1
Set DestLoc = DestSh.Range("A" & NewRowDest)
End If

LastRowSource = sh.Range("A" & Rows.Count).End(xlUp).Row

If LastRowSource + LastRowDest DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
Exit For
End If

sh.Range(MyRangeAddr).Copy
With DestLoc
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With

End If
Next

Application.Goto DestSh.Cells(1)
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--------------------

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183175

Microsoft Office Help

--

Dave Peterson
.


--

Dave Peterson