I have following VBA code:

Dim wsSource As Worksheet, wsTarget As Worksheet
Dim rFrom As Range, rTo As Range
Dim eRow As Long

Set wsSource = Sheets("GRADE")
Set wsTarget = Sheets(wsDestination)
wsTarget.Range("G12:Z200").ClearFormats
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
Set rFrom = wsSource.Range("B12:F" & eRow)
Set rTo = wsTarget.Range("B12")

rFrom.Copy Destination:=rTo

The code works, but I got following fail message sometimes.

Run-time error '-2147417848 (80010108)'

Method 'Copy' of objecgt 'Range' failed


The excel crashed if I got this fail.

It seems that my range does not have data to copy from, but I check my
spreadsheet. There are data on it.

What I should check for above case?
What may go wrong?


Any information is great appreciated,

Where have you defined (DIM'd) wsDestination?
Also,
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
will implicitly assume that Rows.Count is referring to the ActiveSheet ...
is that what you want?

So, it might be mattering which sheet you are on when you run the macro


"Souris" wrote in message
...
I have following VBA code:

Dim wsSource As Worksheet, wsTarget As Worksheet
Dim rFrom As Range, rTo As Range
Dim eRow As Long

Set wsSource = Sheets("GRADE")
Set wsTarget = Sheets(wsDestination)
wsTarget.Range("G12:Z200").ClearFormats
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
Set rFrom = wsSource.Range("B12:F" & eRow)
Set rTo = wsTarget.Range("B12")

rFrom.Copy Destination:=rTo

The code works, but I got following fail message sometimes.

Run-time error '-2147417848 (80010108)'

Method 'Copy' of objecgt 'Range' failed


The excel crashed if I got this fail.

It seems that my range does not have data to copy from, but I check my
spreadsheet. There are data on it.

What I should check for above case?
What may go wrong?


Any information is great appreciated,

wsDestination needs to be defined.
Rows.Count will return 65536 from any worksheet - but if you apply this when
any other type of sheet is active eg a chart you will get an error!
If you are not explicitly Activating the source sheet then change the eRow
assignment to

with wsSource
erow = .cells(.rows.count,2).end(xlup).row
end with


--
Cheers
Nigel



"William Benson" wrote in message
...
Where have you defined (DIM'd) wsDestination?
Also,
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
will implicitly assume that Rows.Count is referring to the ActiveSheet ...
is that what you want?

So, it might be mattering which sheet you are on when you run the macro


"Souris" wrote in message
...
I have following VBA code:

Dim wsSource As Worksheet, wsTarget As Worksheet
Dim rFrom As Range, rTo As Range
Dim eRow As Long

Set wsSource = Sheets("GRADE")
Set wsTarget = Sheets(wsDestination)
wsTarget.Range("G12:Z200").ClearFormats
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
Set rFrom = wsSource.Range("B12:F" & eRow)
Set rTo = wsTarget.Range("B12")

rFrom.Copy Destination:=rTo

The code works, but I got following fail message sometimes.

Run-time error '-2147417848 (80010108)'

Method 'Copy' of objecgt 'Range' failed


The excel crashed if I got this fail.

It seems that my range does not have data to copy from, but I check my
spreadsheet. There are data on it.

What I should check for above case?
What may go wrong?


Any information is great appreciated,

Your code works fine for me. Let me however observe that you could do
with some simplification, weeding out the intermediate assignments.
See:

Sub copyOver()

Dim n As Long

n = Sheets("GRADE").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("GRADE").Range("B12:F" & n).Copy
Sheets(wsDestination).Range("B12")

End sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=399357

How did you get this to work when wsDestination is undefined??


"davidm" wrote in
message ...

Your code works fine for me. Let me however observe that you could do
with some simplification, weeding out the intermediate assignments.
See:

Sub copyOver()

Dim n As Long

n = Sheets("GRADE").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("GRADE").Range("B12:F" & n).Copy
Sheets(wsDestination).Range("B12")

End sub


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=399357

Thanks for the information,

wsDestination is a string and passed from calling function.

wsDestination spreadsheet is active.
I have right erow value.

Thanks again,


"William Benson" wrote:

Where have you defined (DIM'd) wsDestination?
Also,
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
will implicitly assume that Rows.Count is referring to the ActiveSheet ...
is that what you want?

So, it might be mattering which sheet you are on when you run the macro


"Souris" wrote in message
...
I have following VBA code:

Dim wsSource As Worksheet, wsTarget As Worksheet
Dim rFrom As Range, rTo As Range
Dim eRow As Long

Set wsSource = Sheets("GRADE")
Set wsTarget = Sheets(wsDestination)
wsTarget.Range("G12:Z200").ClearFormats
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
Set rFrom = wsSource.Range("B12:F" & eRow)
Set rTo = wsTarget.Range("B12")

rFrom.Copy Destination:=rTo

The code works, but I got following fail message sometimes.

Run-time error '-2147417848 (80010108)'

Method 'Copy' of objecgt 'Range' failed


The excel crashed if I got this fail.

It seems that my range does not have data to copy from, but I check my
spreadsheet. There are data on it.

What I should check for above case?
What may go wrong?


Any information is great appreciated,

Yes, the code works.
The problem is it does not fail all the time.
It only fails, sometimes.

Thanks for the information,


"davidm" wrote:


Your code works fine for me. Let me however observe that you could do
with some simplification, weeding out the intermediate assignments.
See:

Sub copyOver()

Dim n As Long

n = Sheets("GRADE").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("GRADE").Range("B12:F" & n).Copy
Sheets(wsDestination).Range("B12")

End sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=399357

My macro is on source worksheet.
Destination is active.

Does copy method need to run on destination spreadsheet?
Both source and destination spreadsheets are normal spreadsheets.

Anything I need check?
Any information is great appreciated,


"William Benson" wrote:

Where have you defined (DIM'd) wsDestination?
Also,
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
will implicitly assume that Rows.Count is referring to the ActiveSheet ...
is that what you want?

So, it might be mattering which sheet you are on when you run the macro


"Souris" wrote in message
...
I have following VBA code:

Dim wsSource As Worksheet, wsTarget As Worksheet
Dim rFrom As Range, rTo As Range
Dim eRow As Long

Set wsSource = Sheets("GRADE")
Set wsTarget = Sheets(wsDestination)
wsTarget.Range("G12:Z200").ClearFormats
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
Set rFrom = wsSource.Range("B12:F" & eRow)
Set rTo = wsTarget.Range("B12")

rFrom.Copy Destination:=rTo

The code works, but I got following fail message sometimes.

Run-time error '-2147417848 (80010108)'

Method 'Copy' of objecgt 'Range' failed


The excel crashed if I got this fail.

It seems that my range does not have data to copy from, but I check my
spreadsheet. There are data on it.

What I should check for above case?
What may go wrong?


Any information is great appreciated,

I found the problem.
Copy method writes to protect cells.
It works when I remove the protect cells.

Thanks for helping,



"William Benson" wrote:

Where have you defined (DIM'd) wsDestination?
Also,
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
will implicitly assume that Rows.Count is referring to the ActiveSheet ...
is that what you want?

So, it might be mattering which sheet you are on when you run the macro


"Souris" wrote in message
...
I have following VBA code:

Dim wsSource As Worksheet, wsTarget As Worksheet
Dim rFrom As Range, rTo As Range
Dim eRow As Long

Set wsSource = Sheets("GRADE")
Set wsTarget = Sheets(wsDestination)
wsTarget.Range("G12:Z200").ClearFormats
eRow = wsSource.Cells(Rows.Count, 2).End(xlUp).Row
Set rFrom = wsSource.Range("B12:F" & eRow)
Set rTo = wsTarget.Range("B12")

rFrom.Copy Destination:=rTo

The code works, but I got following fail message sometimes.

Run-time error '-2147417848 (80010108)'

Method 'Copy' of objecgt 'Range' failed


The excel crashed if I got this fail.

It seems that my range does not have data to copy from, but I check my
spreadsheet. There are data on it.

What I should check for above case?
What may go wrong?


Any information is great appreciated,