Using named ranges in macro

[BeSmart]

Hi All
I'm a novice trying to adapt code that I found on
http://www.rondebruin.nl/copy2.htm (which is really good), but I'm having a
little trouble and I have Two questions:

1. I need to merge data within all worksheets EXCEPT:
Sheet("Overview")
Hidden sheets
Destination Sheet
I tried to add to the existing code that excludes the destination sheet &
hidden sheets, but my change didn't work?

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

2. I want to include a named range in my code instead of actual cell ranges
i.e.
Range("A18:BJ39") = "GRPResults" named range
This named range is the same across all 6 worksheets that need to be merged

I'm thinking that it's better to use a named range because it will
automatically expand if the user inserts rows - whereas a set cell range will
not?
Please can you show me how the following code should look using the named
ranges versus an actual range (occurs twice in the code before):

Sub CopyGRPSections()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long
Dim lastrow As Variant

lastrow = Range("A40")
Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("GRP Data Collection").Select
Cells.Select
Selection.Clear
Range("A1").Select

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

''''I need to loop through all worksheets but exclude those hidden, the
Overview & Destination sheet
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = "Overview" And sh.Name <> DestSh.Name And sh.Visible = True Then
'''''do nothing

''''''' How do I change the range to Go to "GRPResults" named range in all
worksheets that meet the above criteria?
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
Range("A18:BJ39").Select
Selection.Copy
With DestSh.Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
Last = DestSh.Range("A" & Rows.Count).End(xlUp).Row
shLast = sh.Range("A" & Rows.Count).End(xlUp).Row

''''''' How do I change the range to Go to "GRPResults" named range in all
worksheets that meet the above criteria?
If shLast > 0 And shLast >= StartRow Then
Set CopyRng = sh.Range("A18:BJ39")

If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
Next
ExitTheSub:
Application.Goto DestSh.Cells(1)
Application.ScreenUpdating = True
Application.EnableEvents = True
End With
End Sub

 

[joel]

Try this code



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")

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("GRPResults").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



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

 

[BeSmart]

Thanks Joel

The code looks good and I can understand everything that it's doing.

But I got a run-time error '1004' when I ran it the first time?
Msg: "Method 'Range" of Object '_Worksheet' failed"

This happened at the following point in the code:
sh.range('GRPResults').Copy

The named range is definitely set on two of the worksheets that the code
scans and both worksheets have the same range defined under that name i.e
(A18:BJ39).

Any ideas on how to fix this?

 

[joel]

I never tried to have the same named range on my than one sheet. Exce
only allows one Named Range with the same name. You must have the name
range set to one of the sheets. So you need to remove the shee
reference of the Named Range. Using the Address Property will do tha
like in the code below. The frist two lines of the "TO" is common an
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
--------------------

 

[Dave Peterson]

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.

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/showthread.php?t=183175

Microsoft Office Help

 

[Dave Peterson]

First, it has double quotes around that name:
sh.Range("GRPResults").Copy

Second, you don't have a name on that sheet--or the name doesn't refer to a
range.

You could add a few checks to make sure you know when there's problems--or if to
ignore that sheet.

dim TestRng as range
....

Set testrng = nothing
on error resume next
set testrng = sh.Range("GRPResults")
on error goto 0

if testrng is nothing then
'it wasn't correct
msgbox sh.name & " has a problem with grpresults"
else
'do the real work here
testrng.Copy 'might as well use that variable!
....


That means that you don't have a name

 

[BeSmart]

Thanks Joel

I got another error on your revised code too:
Run-time error '1004'
"Method 'Range' of Object '_Global' failed"

However - I've know why it happened...
I was on the worksheet "GRP Data Collection" when I ran the macro, and this
worksheet does not have the named range "GRPResults".

When I selected a worksheet that does have the named range, and ran the
macro, it ran perfectly!!!
THANK YOU THANK YOU THANK YOU...

Unfortunately, I can't trust the users to select a particular worksheet
before running the macro...

To avoid the debug in the future, should I add the named range to "all"
sheets (which won't affect the ones that are excluding when the macro runs)
or is there a better way to safeguard against the debug happening?

I can't add "Sheets("Plan 1").Select" to the code, because I don't know what
names the users will use on the worksheets.

FYI Re: the same name range on more than one sheet.
Users will make a copy of a master worksheet within the workbook, rename the
copy and then use it.
The master worksheet has the named range, so when the user makes a copy -
the named range copies over too - thereby creating two sheets with the same
named range.

Excel seems to only show the named range for the worksheet selected, plus it
references the sheet name when you look at the list under insert/name/define
(in Excel 2003).

Thank again for your help with this!
BeSmart

 

[BeSmart]

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

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.

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/showthread.php?t=183175

Microsoft Office Help

 

[Dave Peterson]

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.

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

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.

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/showthread.php?t=183175

Microsoft Office Help