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.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
k = k + j + 1
Cells(k, 1).Select
n = n + 3
End Sub

Jim Berglund



David Sauder

I can't replicate this on my PC. Isn't that always the way! Some
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.




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
end with


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question