The following code has been used successfully in Excel 2000 for several
years. My company is beginning to transition users to Excel 2003 and
users are calling me to fix errors on several parts of my code. Here
is one example:


With Sheets(datasheet).Range("A1").CurrentRegion
.Offset(, (ReportDataToUseColumnArray(x,
ReportDataColumnInDb)) - 1).Resize(, 1).Copy _
Sheets("Report").Range(startCell).Offset(, x -
omitIndex)
End With


The message is visual basic error #400

To solve the problem, I added some error code "Resume Next" and the
macro runs just fine. Everything that is supposed to be copied, copies
just fine, but without the "Resume Next" the code errors out.

any suggestions?

Hi Vbjenny,

The following code has been used successfully in Excel 2000 for several
years. My company is beginning to transition users to Excel 2003 and
users are calling me to fix errors on several parts of my code. Here
is one example:


I've seen trouble like this being caused by worksheet protection
(strictly in Excel 2003, the UserinterfaceOnly trick sometimes doesn't
seem to work). If you remove protection before the code and put it back
in place afterwards, I expect you'll be OK.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

to my knowledge there isn't protection on any of the worksheets. I am
running the code from one worksheet module and then copying the data
onto another worksheet in the workbook. But I checked under
tools\protection and I didn't find any protection set. I do password
my code. but I don't believe that is what you are talking about

Hi Vbjenny,

to my knowledge there isn't protection on any of the worksheets.


OK, pity. It was worth mentioning.

Where (what module type) is your code?
It might help to have it in a normal module.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

I did a little more testing and have come to the following conclusion:

Sub test()
Sheets("Sheet1").Range("A1").CurrentRegion.Copy _
Sheets("Sheet2").Range("A1")
End Sub

the above code worked for several years in previous versions of Excel
but produces errors in Excel 2003. The errors occur when the data is
filtered and the data to be copied is made op of non-contiguous rows.

Example:

1. after filtering you want to copy rows 1,5,8,13,23 then there is an
error message

2. after filtering you want to copy rows 1,2,3,4,5,6 then the code
runs fine (NO ERROR)


thanks, Jenny