+ Reply to Thread
Results 1 to 5 of 5

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
    Peter Huang [MSFT]
    Guest

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

    Hi

    Based on my test, it seems that I can not reproduce the problem, can you
    provide a simple test excel worksheet file and attached in the newsgroup
    together with the reproduce code, data and steps. So that I can reproduce
    the problem at my side.

    Also the 1004 is general error, is there any detailed error message?

    Best regards,

    Perter Huang
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "AS IS" with no warranties, and confers no rights.


  3. #3
    TechFirm
    Guest

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

    I would not be able to post the excact excel file since it is a department
    income statement and has sensitive data in it for my client. The data comes
    out of a SQL database at the time the report is ran and has about 5000
    records that are being copied over to the temp1 sheet after the filter is
    applied. I noticed that at the time of the runtime error, the data is
    copied to the temp sheet. If I use the code "on error continue" the report
    will run in Windows 2003 okay. Still not sure why it runs okay in XP but not
    in 2003. I suspect that there must be some record of data being copied that
    it does not like. For now, I have this workaround.

    Thank you for your assistance in this matter.



    ""Peter Huang" [MSFT]" wrote:

    > Hi
    >
    > Based on my test, it seems that I can not reproduce the problem, can you
    > provide a simple test excel worksheet file and attached in the newsgroup
    > together with the reproduce code, data and steps. So that I can reproduce
    > the problem at my side.
    >
    > Also the 1004 is general error, is there any detailed error message?
    >
    > Best regards,
    >
    > Perter Huang
    > Microsoft Online Partner Support
    >
    > Get Secure! - www.microsoft.com/security
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >


  4. #4
    TechFirm
    Guest

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

    I meant running in Office 2003 okay. I am running the program on a Windows XP
    Pro OS, not Windows 2003 server.


    "TechFirm" wrote:

    > I would not be able to post the excact excel file since it is a department
    > income statement and has sensitive data in it for my client. The data comes
    > out of a SQL database at the time the report is ran and has about 5000
    > records that are being copied over to the temp1 sheet after the filter is
    > applied. I noticed that at the time of the runtime error, the data is
    > copied to the temp sheet. If I use the code "on error continue" the report
    > will run in Windows 2003 okay. Still not sure why it runs okay in XP but not
    > in 2003. I suspect that there must be some record of data being copied that
    > it does not like. For now, I have this workaround.
    >
    > Thank you for your assistance in this matter.
    >
    >
    >
    > ""Peter Huang" [MSFT]" wrote:
    >
    > > Hi
    > >
    > > Based on my test, it seems that I can not reproduce the problem, can you
    > > provide a simple test excel worksheet file and attached in the newsgroup
    > > together with the reproduce code, data and steps. So that I can reproduce
    > > the problem at my side.
    > >
    > > Also the 1004 is general error, is there any detailed error message?
    > >
    > > Best regards,
    > >
    > > Perter Huang
    > > Microsoft Online Partner Support
    > >
    > > Get Secure! - www.microsoft.com/security
    > > This posting is provided "AS IS" with no warranties, and confers no rights.
    > >
    > >


  5. #5
    Peter Huang [MSFT]
    Guest

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

    Hi

    I think that may some data format which may related with the issue.
    Anyway, I am glad you have workaround the problem, if you still have any
    concern, please feel free to post here.

    Best regards,

    Perter Huang
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "AS IS" with no warranties, and confers no rights.


+ 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