Error 1004 - again

[Jim Berglund]

Running the following code yields a 1004 error at the noted line. I've tried everything I can think of - any ideas, please?
Option Explicit
Sub Create_Telephone_List()
Dim i, k, m As Long
Dim n, j As Integer
Dim s, r, t, u, v, w, x As String

Dim ws1, ws2 As Worksheet
Application.ScreenUpdating = False

Set ws1 = Sheets("Telephone List")
Set ws2 = Sheets("Telephone Directory")
k = 0 'Locater Row for paste
n = 1 'Column number
For i = 1 To 26
ws1.Activate
ws1.Cells(3, n).Select
j = ActiveCell.CurrentRegion.Rows.Count 'number of rows
ws1.Range(Cells(2, n), Cells(j + 3, n + 1)).Select 'ERROR 1004
Selection.Copy
ws2.Select
k = k + j + 1
Cells(k, 1).Select
ActiveSheet.Paste
n = n + 3
Next
End Sub

Thanks,
Jim Berglund

 

[David Sauder]

I can't replicate this on my PC. Isn't that always the way! Some
possibilities:
You can get error 1004 when selecting a range if the code is being
called by a button - or if some object on the sheet is selected. The
code has to explicitly deselect the button/object before selecting
other ranges. However, in this case you have some range selection
code in an earlier line so that may not be it.

Is it possible you have some unusual values for j showing up? What
happens if you replace the j and n in this line with values (e.g.
ws1.Range(Cells(2, 3), Cells(2 + 3, 2 + 1)).Select

If that works, then the problem must be with j or n, and you can track
it down from there.

David Sauder

 

[David Sauder]

Well, I'm out of ideas. If you want, you could send me a copy (after
taking out any confidential information, of course), and I'll see if
it works on my machine.

dsauderathiddenlogic.com

David

 

[Dave Peterson]

Qualify your ranges -- including the cells().
ws1.Range(ws1.Cells(2, 1), ws1.Cells(22, 2)).Select

But since ws1 has to be active to select a range, maybe:

with ws1
.select
.range(.cells(2,1),.cells(22,2)).select
end with

ps.

These newsgroups are plain text. Lots of people will ignore messages in
HTML/RTF or messages that include attachments.

(I know I ignored your message until today--those HTML messages are hard to
read!)