+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    TechFirm
    Guest

    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")



  2. #2
    Colo
    Guest

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

    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" <techfirm@online.nospam> wrote in message
    news:#voo$0i$EHA.1260@TK2MSFTNGP12.phx.gbl...
    > 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")
    >
    >



  3. #3
    TechFirm
    Guest

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

    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" <techfirm@online.nospam> wrote in message
    > news:#voo$0i$EHA.1260@TK2MSFTNGP12.phx.gbl...
    > > 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")
    > >
    > >

    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1