Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

copy range error

  Asked By: Ryan    Date: Sep 13    Category: MS Office    Views: 1571
  

I am having the following error.
Run-time error ¡¥1004¡¦
Application defined or object-defined error
This error is coming at the line marked with arrow in the code
pasted below.

I am basically trying to copy the range from one sheet and paste it
in other sheet. My code is pasted below. I picked up this code from
the Walkenbach book and also from the post replies by Pascal on this
group site.
Can any one help me as to what is going wrong here?

Sub RevPopulateDatabaseCorpCpn()
Dim a As Date
Dim z As Double 'count row# for sheet DatabaseCorpCpn
z = 2

a = Sheets("RevDatabaseCorpCpn").Cells(2, 1).Value
Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value <= a
z = z + 1
Loop

Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value > a
Sheets("RevDatabaseCorpCpn").Cells(2, 1).Select
Selection.EntireRow.Insert Shift:=xlDown
„³Sheets("DatabaseCorpCpn").Range(Cells(z, 1), Cells(z, 96)).Select
Selection.Copy
Sheets("RevDatabaseCorpCpn").Range(Cells(2, 1), Cells(2, 96)).Select
ActiveSheet.Paste
a = Sheets("RevDatabaseCorpCpn").Cells(2, 1).Value
z = z + 1
Loop

End Sub

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Dallas Martin     Answered On: Sep 13

The reason is that excel doesn't like selecting things on a sheet
which isn't the active sheet.
Also important in this case, from excel help:
------------------------------------------------------------
Range and Cells
Use Range(cell1, cell2), where cell1 and cell2 are range  objects that
specify the start and end cells, to return a Range object. The
following example sets the border line  style for cells A1:J10.

With Worksheets(1)
.Range(.Cells(1, 1),.Cells(10, 10)).Borders.LineStyle = xlThick
End With

Notice the period in front of each occurrence of the Cells property.
The period is required if the result of the preceding With statement
is to be applied to the Cells property— in this case, to indicate that
the cells are on worksheet one (without the period, the Cells property
would return cells on the active sheet).
-------------------------------------------------------------

Those last 5 words..
The quickest solution, as far as coding time goes, is to activate the
sheet before referring to those cells:
Sub RevPopulateDatabaseCorpCpn()
Dim a As Date
Dim z As Double 'count row#  for sheet  DatabaseCorpCpn

z = 2
a = Sheets("RevDatabaseCorpCpn").Cells(2, 1).Value
Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value <= a
z = z + 1
Loop

Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value > a

Sheets("RevDatabaseCorpCpn").Activate
Sheets("RevDatabaseCorpCpn").Cells(2, 1).Select
Selection.EntireRow.Insert Shift:=xlDown

Sheets("DatabaseCorpCpn").Activate
Sheets("DatabaseCorpCpn").Range(Cells(z, 1), Cells(z, 96)).Select
Selection.Copy

Sheets("RevDatabaseCorpCpn").Activate
Sheets("RevDatabaseCorpCpn").Range(Cells(2, 1), Cells(2, 96)).Select
ActiveSheet.Paste
a = Sheets("RevDatabaseCorpCpn").Cells(2, 1).Value
z = z + 1
Loop

End Sub

However, you could then do away with the explicit worksheet refs.:

Sub RevPopulateDatabaseCorpCpn()
Dim a As Date
Dim z As Double 'count row# for sheet DatabaseCorpCpn

z = 2
a = Sheets("RevDatabaseCorpCpn").Cells(2, 1).Value
Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value <= a
z = z + 1
Loop

Do While Sheets("DatabaseCorpCpn").Cells(z, 1).Value > a

Sheets("RevDatabaseCorpCpn").Activate
Cells(2, 1).Select
Selection.EntireRow.Insert Shift:=xlDown

Sheets("DatabaseCorpCpn").Activate
Range(Cells(z, 1), Cells(z, 96)).Select
Selection.Copy

Sheets("RevDatabaseCorpCpn").Activate
Range(Cells(2, 1), Cells(2, 96)).Select
ActiveSheet.Paste
a = Cells(2, 1).Value
z = z + 1
Loop

End Sub

Throw in a Application.Screenupdating=False and that gets rid of your
screen flicker.
I'd prefer to do away with all the sheet activating and range
selecting. This means no screen flicker to consider, much faster
execution (5 times faster), and successful execution regardless of
which sheet is active (even if it's not one of the two).
While I was at it I'd define the worksheets as objects to make the
code more readable.

Sub RevPopulateDatabaseCorpCpn()
Dim a As Date
Dim z As Double 'count row# for sheet DatabaseCorpCpn
Dim shtFrom As Worksheet, shtTo As Worksheet
Set shtFrom = Sheets("DatabaseCorpCpn")
Set shtTo = Sheets("RevDatabaseCorpCpn")

z = 2
a = shtTo.Cells(2, 1).Value
Do While shtFrom.Cells(z, 1).Value <= a
z = z + 1
Loop

Do While shtFrom.Cells(z, 1).Value > a
shtTo.Cells(2, 1).EntireRow.Insert Shift:=xlDown

shtTo.Range(shtTo.Cells(2, 1), shtTo.Cells(2, 96)) = _
shtFrom.Range(shtFrom.Cells(z, 1), shtFrom.Cells(z, 96)).Value

a = shtTo.Cells(2, 1).Value
z = z + 1
Loop

End Sub


Finally,

shtTo.Range(shtTo.Cells(2, 1), shtTo.Cells(2, 96)) = _
shtFrom.Range(shtFrom.Cells(z, 1), shtFrom.Cells(z, 96)).Value

can be replaced with

shtFrom.Range(shtFrom.Cells(z, 1), shtFrom.Cells(z, 96)) _
.Copy shtTo.Range(shtTo.Cells(2, 1), shtTo.Cells(2, 96))

which I *think* might copy  formulae rather than values but if there
are no formulae it shouldn't be any different.

 
Didn't find what you were looking for? Find more on copy range error Or get search suggestion and latest updates.




Tagged: