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

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


On Apr 15, 1:06 pm, "Jim Berglund" wrote:
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

I have j & n Dimensioned as Integers, and both have been initialized with integer values. I tried your suggestion of substituting values

ws1.Range(Cells(2, 1), Cells(22, 2)).Select

but it still won't work.
Could it be a problem with Excel?

Jim

"David Sauder" wrote in message ...
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


On Apr 15, 1:06 pm, "Jim Berglund" wrote:
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

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

On Apr 16, 9:31 am, "Jim Berglund" wrote:
I have j & n Dimensioned as Integers, and both have been initialized with integer values. I tried your suggestion of substituting values

ws1.Range(Cells(2, 1), Cells(22, 2)).Select

but it still won't work.
Could it be a problem with Excel?

Jim

"David Sauder" wrote in ...
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

On Apr 15, 1:06 pm, "Jim Berglund" wrote:
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

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


Jim Berglund wrote:

I have j & n Dimensioned as Integers, and both have been initialized with
integer values. I tried your suggestion of substituting values

ws1.Range(Cells(2, 1), Cells(22, 2)).Select

but it still won't work.
Could it be a problem with Excel?

Jim

"David Sauder" wrote in message
...
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


On Apr 15, 1:06 pm, "Jim Berglund" wrote:
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


--

Dave Peterson