MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

PasteSpecial in VB6

  Asked By: Cory    Date: Nov 20    Category: MS Office    Views: 3415

I've got a spreadsheet that I'm manipulating
with VB-6. I've can't seem to get PasteSpecial
to work.

I recorded a macro in the spreadsheet.
Here is the VBA code.

Selection.PasteSpecial _
Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _

Here is what I came up with in VB6 ...

Dim MyXL As Excel.Workbook
Dim MyXLSh As Excel.Worksheet
Set MyXL = GetObject(" -- file name -- ")
Set MyXLSh = MyXL.Worksheets(1)
' so far, so good.. I've done the above many times
' now, here's the problem area
With MyXLSh
.Copy <------------------------- weird background colors happen here
.Range("C6").Select <----------- crashes here
.PasteSpecial xlPasteFormulas <- never got this far !!
End With

I did ".Copy" and not ".Selection.Copy" because
the latter was not available from the tool-tips
(and thus, not recognized by VB6).

I left out "Paste:=xlPasteFormulas, (etc)" as I
got a compiler error (operation not recognized).

Any ideas?



3 Answers Found

Answer #1    Answered By: Adalwolfa Fischer     Answered On: Nov 20

in your vb6  prog...try using...

With MyXLSh
.Range("C6").PasteSpecial xlPasteFormulas
End With

hope it works...

Answer #2    Answered By: Rebekah Walker     Answered On: Nov 20

It works like a champ.
I had a good  feeling as I was entering the code..
.. there was even a tool-tip for the "xlPasteFormulas".

And znapp .. thanks for your suggestion, but
all that did was to copy  the value, not the formula.

Answer #3    Answered By: Fadwa Ahmed     Answered On: Nov 20

MyXLSh.Range("C6") = MyXLSh.Range("C5")
instead of copy  & paste

Didn't find what you were looking for? Find more on PasteSpecial in VB6 Or get search suggestion and latest updates.