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

[HansV]

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?

[ase001]

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

[ase001]

(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

[sdckapr]

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

[ase001]

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

[sdckapr]

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

Steve

[ase001]

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

[AndrewO]

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

[ase001]

(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

[ase001]

Thanks again Hans.

[HansV]

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