Excel 2003 Issue with UsedRange.Copy (code works in Excel 2002)

I have been running the below code in an Excel Macro without any issues to
copy selective rows of data to a temp1 worksheet based upon a autofilter
selection. After I went to Office 2003 Professional for Office XP Pro, I
get a run-time error '1004' on the line for the UsedRange.Copy. Does any
one know of a fix or why this is happening?

'Filter and copy code:
If Worksheets(GLBalance).FilterMode = True Then
Worksheets(GLBalance).ShowAllData
End If
Worksheets(GLBalance).Range(ColGLBalCompanyCode & "1").AutoFilter _
field:=1, _
Criteria1:=szCompanyCode
Worksheets(GLBalance).Range(ColGLBalYear & "1").AutoFilter _
field:=3, _
Criteria1:=nYear
Worksheets(GLBalance).Range(ColGLBalPeriod & "1").AutoFilter _
field:=4, _
Criteria1:="<=" & szPeriod
Worksheets(Temp1).Cells.ClearContents
ActiveWorkbook.Worksheets(GLBalance).Activate
ActiveWorkbook.Worksheets(GLBalance).Cells.SpecialCells(xlCellTypeVisible).S
elect
ActiveWorkbook.Worksheets(GLBalance).UsedRange.Copy _
Destination:=ActiveWorkbook.Worksheets(Temp1).Range("A1")

Since I don't use Windows XP, I could not try your code on the appropriate
environment.
But the following code works on Excel 2003.

'Copy the filtered range
Worksheets(GLBalance).AutoFilter.Range.Copy Worksheets(Temp1).Range("A1")

PS
I just thought that you can use the code using "AutoFilterMode" property
something like this..

Worksheets(GLBalance).AutoFilterMode = False

instead of

If Worksheets(GLBalance).FilterMode = True Then
Worksheets(GLBalance).ShowAllData
End If

--
Regards,
Colo
http://www.puremis.net/excel/


"TechFirm" <[email protected]> wrote in message
news:#[email protected]...
> I have been running the below code in an Excel Macro without any issues to
> copy selective rows of data to a temp1 worksheet based upon a autofilter
> selection. After I went to Office 2003 Professional for Office XP Pro, I
> get a run-time error '1004' on the line for the UsedRange.Copy. Does any
> one know of a fix or why this is happening?
>
> 'Filter and copy code:
> If Worksheets(GLBalance).FilterMode = True Then
> Worksheets(GLBalance).ShowAllData
> End If
> Worksheets(GLBalance).Range(ColGLBalCompanyCode & "1").AutoFilter _
> field:=1, _
> Criteria1:=szCompanyCode
> Worksheets(GLBalance).Range(ColGLBalYear & "1").AutoFilter _
> field:=3, _
> Criteria1:=nYear
> Worksheets(GLBalance).Range(ColGLBalPeriod & "1").AutoFilter _
> field:=4, _
> Criteria1:="<=" & szPeriod
> Worksheets(Temp1).Cells.ClearContents
> ActiveWorkbook.Worksheets(GLBalance).Activate
>
ActiveWorkbook.Worksheets(GLBalance).Cells.SpecialCells(xlCellTypeVisible).S
> elect
> ActiveWorkbook.Worksheets(GLBalance).UsedRange.Copy _
> Destination:=ActiveWorkbook.Worksheets(Temp1).Range("A1")
>
>

I will give your code change a try in the morning. It is strange that it
works in XP but not in 2003. If I continue when the error occures, the data
is copied and it runs okay there after. This might be the only work around I
have for this problem.

Thank you for you assistance in this matter.





"Colo" wrote:

> Since I don't use Windows XP, I could not try your code on the appropriate
> environment.
> But the following code works on Excel 2003.
>
> 'Copy the filtered range
> Worksheets(GLBalance).AutoFilter.Range.Copy Worksheets(Temp1).Range("A1")
>
> PS
> I just thought that you can use the code using "AutoFilterMode" property
> something like this..
>
> Worksheets(GLBalance).AutoFilterMode = False
>
> instead of
>
> If Worksheets(GLBalance).FilterMode = True Then
> Worksheets(GLBalance).ShowAllData
> End If
>
> --
> Regards,
> Colo
> http://www.puremis.net/excel/
>
>
> "TechFirm" <[email protected]> wrote in message
> news:#[email protected]...
> > I have been running the below code in an Excel Macro without any issues to
> > copy selective rows of data to a temp1 worksheet based upon a autofilter
> > selection. After I went to Office 2003 Professional for Office XP Pro, I
> > get a run-time error '1004' on the line for the UsedRange.Copy. Does any
> > one know of a fix or why this is happening?
> >
> > 'Filter and copy code:
> > If Worksheets(GLBalance).FilterMode = True Then
> > Worksheets(GLBalance).ShowAllData
> > End If
> > Worksheets(GLBalance).Range(ColGLBalCompanyCode & "1").AutoFilter _
> > field:=1, _
> > Criteria1:=szCompanyCode
> > Worksheets(GLBalance).Range(ColGLBalYear & "1").AutoFilter _
> > field:=3, _
> > Criteria1:=nYear
> > Worksheets(GLBalance).Range(ColGLBalPeriod & "1").AutoFilter _
> > field:=4, _
> > Criteria1:="<=" & szPeriod
> > Worksheets(Temp1).Cells.ClearContents
> > ActiveWorkbook.Worksheets(GLBalance).Activate
> >
> ActiveWorkbook.Worksheets(GLBalance).Cells.SpecialCells(xlCellTypeVisible).S
> > elect
> > ActiveWorkbook.Worksheets(GLBalance).UsedRange.Copy _
> > Destination:=ActiveWorkbook.Worksheets(Temp1).Range("A1")
> >
> >
>
>