Excel 2003 macro from Excel 2000 doesn't work

[Guest]

This is the portion of the macro that no longer works in EXCEL 2003. I have
tried the selection.copy in so many different ways. I was getting an error
of invalid range - in subsequent tests I got other messges - the last few
times the A2 cell on the "random" sheet was showing active and the sheet was
blank although there was data on the sheet I was copying from. Other macros
read in a file and do a random 10% selection of the records and flags them as
audit in the A column. In Excel 2000 I was able to copy them into the blank
worksheet (named Random, or sheets(2)) so that I could add a descriptive
heading before printing. When I saved the file in Excel 2003 it no longer
works. Gets to this point and stops. Can any of you tell me why or give me
a workaround. Thanks for your time and consideration. Hoss


Worksheets(3).Range("A1").AutoFilter _
Field:=1, Criteria1:="audit"
'hide column 1 so it won't be selected
Columns("A:A").Select
Selection.EntireColumn.Hidden = True
'select all cells that have a "visible" property.
Range("B1").CurrentRegion.SpecialCells(xlVisible).Select
'Copy the visible cells to worksheet Random.
Selection.Copy Worksheets("Random").Range("A2")

 

[Greg J via OfficeKB.com]

Hi Hoss

I have also been having similar problems going from 97 to 2003. I have put
code samples up on different forums for answers. If you google "problems
using sheets created in 97, in 2003" you will see what help I have received.

In my case it seems like the code in Excel 97 is more forgiving than in
later versions.

This might not answer your question but will give you a few ideas of how to
attack the problem.

Cheers
Greg

 

[Guest]

Thanks for the encouragement Greg - visited the site you suggested but
couldn't find anything. I noticed now when I run the macros that the data
gets moved from the sending sheet to the receiving sheet before I get the
"Run-time error '1004', Copy method of Range class failed" dialog box, but it
also highlights an additional column as though it was also selected. The
data I'm copying has been filtered but that has never been a problem in the
older versions of Excel. Perhaps one of the MVPs will post an explanation.
Until then it's manual processing. Thanks again. Hoss

 

[Greg J via OfficeKB.com]

Hi Hoss

Are you aware you can use "step into" under the debug menu in the Visual
Basic Editor. By using this you can watch a line of code at a time and work
out were the line of code is that is causing the problem.

Cheers
Greg

 

[Greg J via OfficeKB.com]

Hoss
See if this works for you!

'Place the code below into the standard module
'Make sure Sheet 1 is visible when running macro
'Make up a AutoFilter on Sheet 1 with Headings on A1,B1 and C1 -leave D1
free for Criteria


Sub FilterCopying()
Dim rng As Range
With Worksheets("Sheet2")
'Clear any cells on Sheet 2
.Cells.Clear
'Next line sets AutoFilter column 2 to what ever criteria is set in
Range D1
Range("A1").AutoFilter Field:=2, Criteria1:=Range("D1").Value
'Next line selects visible cells on worksheet 1
Set rng = Range("A2").CurrentRegion.SpecialCells(xlCellTypeVisible)
'The next line copies the info to sheet 2
rng.Copy .Range("A1")
'The next line deletes the first line of sheet 2
.Rows(1).Delete
End With
End Sub

Cheers
Greg

 

[Guest]

Thanks Greg. Got the original code to work beautifully by using an On Error
Resume Next statement in the sub procedure where the code was written. The
selection.copy statement works fine then and all the rest of the macros
complete correctly. Got that idea from an article in a Tek-Tips forum. When
I try to get the error number I don't get one. That's going to be my next
project when I have time. Hoss