I did this with two sheets in the sending workbook (sheet1 was hidden) and only
1 sheet in the receiving workbook.

Things got uglier.

That single sheet in the receiving workbook was half hidden and half visible
(kind of). The sheet tabs disappeared. I opened the VBE in the second
instance. Then I went back to excel.

The VBE "bled" into the worksheet window. But I couldn't see anything on the
worksheet grid--but if I changed selection, I could see the outline of the
active cell.

I added another worksheet to the second workbook and the sheet tabs showed
up--but sheet1 wasn't visible.

So I have a question for you.

Are you pasting just the value or do you want to include formatting?

If you're pasting just values, your code could use the windows clipboard.

Option Explicit
Sub testme()

Dim MyDataObj As DataObject
Dim myVal As Variant

Set MyDataObj = New DataObject

myVal = Sheet1.Range("A1").Value
MyDataObj.SetText myVal
MyDataObj.PutInClipboard

End Sub

You'll have to have a reference to "Microsoft Forms 2.0 Object Library" to make
this work (tools|References within the VBE).

You'll want to read Chip Pearson's notes on working with the clipboard:
http://www.cpearson.com/excel/clipboar.htm

Then things _seemed_ to work ok.



wrote:

Hi all,

I'm having a problem with Excel and I haven't been able to find a
solution.

What I'm doing is copying some data from a hidden worksheet using VBA.
This is done very easily. The problem is that Excel copies, together
to the data, also the state of the worksheet (Visible/Hidden/
VeryHidden), and this is causing me headaches. Let me give you the
algorithm to reproduce my problem.

1. Open two indipendent session of Excel. With my current settings it
opens it with three worksheets: Sheet1, Sheet2 and Sheet3.
2. On the first session type a number in cell Sheet1!A1 (for example
878)
3. Press Alt-F11to enter the VBA Editor and change the property
"Visible" for Sheet1 to be xlSheetVeryHidden
4. From the Immediate window type: Sheet1.Range("A1").Copy (this will
copy the content of cell A1 into the clipboard. Unfortunately it
copies something more than that).
5. Go on the second session of Excel and press Ctrl^V (or Paste from
the Edit menu).
6. On the second session of Excel click on the tab of the second
worksheet (Sheet2). You will see that Sheet1 has disappeared.
7. Open Visual Basic Editor on the second session of Excel, and if you
check, the Sheet1 still exists but it is VeryHidden.

The same happens if the source sheet is only Hidden, and it suggests
to me that Sheet1.Range("A1").copy doesn't only copy information about
the current cell, but also other information about the worksheet and
this is where it gets nasty for me. My application generates some data
in a hidden worksheet that the user can export by copying it to the
clipboard (using a button provided). If at that stage the user copies
the data to another session of Excel (which is quite a reasonable
thing to do), the worksheet disappears and the user panics thinking
that all this work has been lost.

And unfortunately, since after the copy everything is in the user's
hands, I cannot force any defensive action at the time the data is
copied (e.g. PasteSpecial-Values), but I need to find a work-around
to the "sheet1.range("A1").copy.

Interestingly enough, this only happens if the data is copied from one
session of Excel to another. Copying and pasting "from and to" the
same workbook or "from and to" two different workbooks open in the
same Excel session works normally as expected.

Any ideas? thank you very much
gc

--

Dave Peterson