Clipboard Question?

[Josh Sale]

Let me start by explaining what I'm trying to accomplish and then I'll
describe my unsuccessful strategy for reaching my goal.

I want to add a new user command to my application (which runs under XL97 -
2003) that will put a range of Excel formulas onto the clipboard so that a
user can, after the command completes, select a range of cells on some other
worksheet and perform a Edit | Paste Special | Formulas. As a special case,
this user command might place a single formula on the clipboard but in the
general case it will put a 2 dimensional array of formulas onto the
clipboard.

My plan of attack after I had generated the array of formulas was to:

- Create a new worksheet.
- Stuff the formulas into the .FormulaR1C1 property of a range of cells on
that new worksheet.
- Perform a .Copy of that range of cells on the new worksheet.
- Delete the worksheet created in the first step.

As you smart guys out there have already figured out, this didn't work. It
turns out that as soon my code deletes the temporary worksheet (which is the
source of the clipboard Copy) it wipes out the clipboard.

I embarked on this approach because in my past poking around with
manipulating the clipboard I never found out how to put an array of stuff
onto the clipboard.

So does anybody know how to accomplish this? I guess I'm looking for some
alternative technique for pushing an array of formulas onto the clipboard.

TIA,

josh

 

[Tom Ogilvy]

put them on a worksheet. Hide the worksheet.

When you need to paste them, copy and paste all together:


worksheets("Hidden1").Range("B9").Resize(20,2).copy
ActiveCell.Pastespecial xlFormulas

otherwise, put them in an array then

ActiveCell.Resize(20,2).Formula = vArr

 

[Josh Sale]

Thanks Tom.

After my posting I changed my code to create a new hidden workbook the first
time it generates these formulas which is then reused on subsequent
attempts. This feels a little better to me than a hidden worksheet since I
don't particularly want the hidden worksheet to be saved with the active
workbook if the user decides to do a Save operation (I know I could cleanup
before save but my approach eliminates that need).

Thanks so much for using Resize in your example! I've been messing around
in the Excel object model for a number of years now and have never noticed
it. When I've had this problem I always painfully build the new range using
Range(TopLeft, TopLeft.Offset(x, y)) kind of code.

josh