Dynamic Range Offset causing problem with this code
 

I have a wb with several sheets, each has a dynamic range with a name
CODExx.

The following code - partly from the posts here - works if I have NONE
dynamic range (=offset(sheets......etc)

Public Sub moveplist()
Dim rcnt As Integer
Dim nme As Variant
For Each nme In ActiveWorkbook.Names
If Left(nme.Name, 4) = "CODE" Then
'Debug.Print nme.Name
'Debug.Print Range("nme.Name")
Range(nme.Name).Copy Destination:= _
Worksheets("PTable").Cells(1 + rcnt,
1).Range(Range(nme.Name).Address)
End If
rcnt = rcnt + Range(Range(nme.Name).Address).Rows.Count
Next nme

End Sub

If I use dynamic range I get an error msg
Rune Time error '1004'
Method 'Range' of Object 'Global' failed

Can you help

Dynamic Range Offset causing problem with this code
 

Arishy,

I couldn't reproduce your error, but I got an error, caused I think by
incrementing the counter outside the If test.

Reversing them solved my error

End If
rcnt = rcnt + Range(Range(nme.Name).Address).Rows.Count

to

rcnt = rcnt + Range(Range(nme.Name).Address).Rows.Count
End If


--
HTH

Bob Phillips

"Arishy" wrote in message
oups.com...
I have a wb with several sheets, each has a dynamic range with a name
CODExx.

The following code - partly from the posts here - works if I have NONE
dynamic range (=offset(sheets......etc)

Public Sub moveplist()
Dim rcnt As Integer
Dim nme As Variant
For Each nme In ActiveWorkbook.Names
If Left(nme.Name, 4) = "CODE" Then
'Debug.Print nme.Name
'Debug.Print Range("nme.Name")
Range(nme.Name).Copy Destination:= _
Worksheets("PTable").Cells(1 + rcnt,
1).Range(Range(nme.Name).Address)
End If
rcnt = rcnt + Range(Range(nme.Name).Address).Rows.Count
Next nme

End Sub

If I use dynamic range I get an error msg
Rune Time error '1004'
Method 'Range' of Object 'Global' failed

Can you help