Results 1 to 11 of 11
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    If I understand you correctly, it is NOT a VBA problem: when connecting through remote desktop, you can't copy a worksheet interactively either. Or am I completely off?

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    That seems to be it Hans,

    On normal desktop I can open the workbook, I select the command button and a new copy of the worksheet is created as a new workbook
    On remote desktop I can open the workbook, I select the command button - error 1004 appears. Worksheet is not apparently copy'able when opening the workbook in remote desktop.

    I think I will have to work around it by creating new workbook and copying all of the sheets data over to the new workbook...unless anyone knows different?

    Alan

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Unable to copy sheet (VBA/Excel/97sr2)

    (Thread moved from VB/VBA to Excel by HansV because it turned out not to be VBA specific.)

    Posted here instead of Excel because I see it more of a vba problem, but if needs moving please advise.

    Excel workbook, has one sheet for which there is a command button with macro:

    Sub Copy_Sheet()
    Dim strMySheet As String
    strMySheet = ActiveSheet.Name
    Sheets(strMySheet).Copy
    End Sub

    It is very straight forward sequence but believe me, the guys operating this worksheet are not able to copy a sheet reliably, hence the command button.

    When the workbook is used under normal circumstances the command works fine and a new worksheet is created.
    However, when connecting through remote desktop the code errors at the .copy line with

    Run Time error 1004
    Copy method of worksheet class failed

    Also, strangely enough, when I manually drag and drop a copy of the worksheet into the frame surround area, it just doesn't copy.

    If I right mouse the worksheet tab and go to make a copy in a new workbook, again, nothing happens.


    Any suggestions?

    TIA
    Alan
    Cheshire
    UK

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    In XL97 there is a bug and some things are NOT allowed when an object has the focus.

    Try adding the line "Activecell.select" as the first line of your code. This ensures that the object which calls the macro does not have the focus.

    Sub Copy_Sheet()
    Activecell.select
    Dim strMySheet As String
    strMySheet = ActiveSheet.Name
    Sheets(strMySheet).Copy
    End Sub

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    Sorry Steve, that didn't work either.

    I tried code to add a new workbook and copy the worksheets cells to that new workbook, but the error occurred again at the line:- "Workbooks.Add"

    Alan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    Is the workbook protected (Tools - protection - (un)protect workbook)?

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    No, workbook is not protected.

    I had normal desktop (XP OS) running and opened the file, ran through all of the code succesfully creating copy sheets as required.

    I closed the file and opened a remote desktop session, opened the same file through this session, and the code error'd.

    T'is most frustrating.
    I suspect users rights?

    alan

  8. #8
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    The only other instances I find when worksheets don't copy is when they're in some state that it is 'greyed out'. For instance when actively editting a text box, or when a dialog is up but hidden behind the window. I'd be tempted to try range("A1").select to make sure

  9. #9
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    In depth search on MS revealed:
    http://support.microsoft.com/default.aspx?...kb;en-us;178510

    Bascially tellin gme that the normal method of referencing needs to be more precise and complete.
    I used the method and I was able to add new workbooks, but.....I cannot SEE those workbooks.
    When I go to log off the remote desktop I am prompted to save the as yet unseen workbooks. Excel is not running and I have no other application open.
    <pre>Sub Copy_Sheet()
    Dim strMySheet As String
    Dim xlApp As Excel.Application
    Dim xlSheet As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")

    xlApp.Workbooks.Add

    End Sub
    </pre>

    Can I reveal these new workbooks by repainting or refreshing somehow?

    Thanks
    Alan
    Cheshire
    UK

  10. #10
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    Thanks again Hans.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Unable to copy sheet (VBA/Excel/97sr2)

    When you start an instance of Excel this way, it is invisible. The prompt to save the workbooks is issued by Excel, which has remained in memory invisibly because you haven't told it to quit. You can make Excel visible by

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True

Posting Permissions

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