# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  > [SOLVED] [SOLVED] PasteSpecial: values and formats possible in vbs?

## D. Pirate Roberts

I use the following code in vbs to copy and paste data from one Excel cell
to another:

XLBook.Worksheets("Sheet1").Select
XLBook.Worksheets("Sheet1").Range("A1").Copy
XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial

My code above does a regular paste, but I need to do a paste special that
only pastes values and number formats. I've created a macro in Excel that
does this and the vba code for it is:

Range("A1").Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

However, the vba code above does not work in vbs. It throws an Expceted
Statement error at the first colon ( :Smilie:  in the PasteSpecial line. Does anyone
know how I can do this in vbs?

----------


## y sakuda

"D. Pirate Roberts" <dreadpirateroberts@bride.com> wrote in message
news:etaUBAdyFHA.1168@TK2MSFTNGP15.phx.gbl...
>I use the following code in vbs to copy and paste data from one Excel cell
>to another:
>
> XLBook.Worksheets("Sheet1").Select
> XLBook.Worksheets("Sheet1").Range("A1").Copy
> XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial
>
Hellow
Try this

XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial -4122

Note: for -4122 search for Object browser with xlpasteformats
Y Sakuda from JPN

----------


## Tom Ogilvy

XLBook.Worksheets("Sheet1").Select
XLBook.Worksheets("Sheet1").Range("A1").Copy
XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial 12

--
regards,
Tom Ogilvy


"D. Pirate Roberts" <dreadpirateroberts@bride.com> wrote in message
news:etaUBAdyFHA.1168@TK2MSFTNGP15.phx.gbl...
> I use the following code in vbs to copy and paste data from one Excel cell
> to another:
>
> XLBook.Worksheets("Sheet1").Select
> XLBook.Worksheets("Sheet1").Range("A1").Copy
> XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial
>
> My code above does a regular paste, but I need to do a paste special that
> only pastes values and number formats. I've created a macro in Excel that
> does this and the vba code for it is:
>
> Range("A1").Select
>     Selection.Copy
>     Range("B1").Select
>     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
>
> However, the vba code above does not work in vbs. It throws an Expceted
> Statement error at the first colon ( :Smilie:  in the PasteSpecial line. Does
anyone
> know how I can do this in vbs?
>
>

----------


## Tom Ogilvy

? xlPasteValuesAndNumberFormats
12
? xlpasteFormats
-4122
? xlpasteValues
-4163


--
Regards,
Tom Ogilvy


"y sakuda" <sakudayasuichiro@hotmail.com> wrote in message
news:%23xeoqIdyFHA.3812@TK2MSFTNGP09.phx.gbl...
> "D. Pirate Roberts" <dreadpirateroberts@bride.com> wrote in message
> news:etaUBAdyFHA.1168@TK2MSFTNGP15.phx.gbl...
> >I use the following code in vbs to copy and paste data from one Excel
cell
> >to another:
> >
> > XLBook.Worksheets("Sheet1").Select
> > XLBook.Worksheets("Sheet1").Range("A1").Copy
> > XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial
> >
> Hellow
> Try this
>
>  XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial -4122
>
> Note: for -4122 search for Object browser with xlpasteformats
>                                                           Y Sakuda from
JPN
>
>

----------


## y sakuda

"y sakuda" <sakudayasuichiro@hotmail.com> wrote in message
news:%23xeoqIdyFHA.3812@TK2MSFTNGP09.phx.gbl...
> "D. Pirate Roberts" <dreadpirateroberts@bride.com> wrote in message
> news:etaUBAdyFHA.1168@TK2MSFTNGP15.phx.gbl...
>
> XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial -4122
>
Sorry you want to Paste values and format.
I misread your post.
-4122 paste format only.
Y Sakuda from JPN

----------


## D. Pirate Roberts

Thanks, Tom - that works perfectly! By the way, is there an online reference
guide that lists the PasteSpecial codes that can be used in this manner?


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OVC0cJdyFHA.2064@TK2MSFTNGP09.phx.gbl...
> XLBook.Worksheets("Sheet1").Select
> XLBook.Worksheets("Sheet1").Range("A1").Copy
> XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial 12
>
> --
> regards,
> Tom Ogilvy
>
>
> "D. Pirate Roberts" <dreadpirateroberts@bride.com> wrote in message
> news:etaUBAdyFHA.1168@TK2MSFTNGP15.phx.gbl...
>> I use the following code in vbs to copy and paste data from one Excel
>> cell
>> to another:
>>
>> XLBook.Worksheets("Sheet1").Select
>> XLBook.Worksheets("Sheet1").Range("A1").Copy
>> XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial
>>
>> My code above does a regular paste, but I need to do a paste special that
>> only pastes values and number formats. I've created a macro in Excel that
>> does this and the vba code for it is:
>>
>> Range("A1").Select
>>     Selection.Copy
>>     Range("B1").Select
>>     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
>> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
>>
>> However, the vba code above does not work in vbs. It throws an Expceted
>> Statement error at the first colon ( :Smilie:  in the PasteSpecial line. Does
> anyone
>> know how I can do this in vbs?
>>
>>
>
>

----------


## Tom Ogilvy

Excel VBA help on Pastespecial

the values for the constants can be seen in the Object Browser in the VBE.

--
Regards,
Tom Ogilvy

"D. Pirate Roberts" <dreadpirateroberts@bride.com> wrote in message
news:Og1AcVdyFHA.2516@TK2MSFTNGP12.phx.gbl...
> Thanks, Tom - that works perfectly! By the way, is there an online
reference
> guide that lists the PasteSpecial codes that can be used in this manner?
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:OVC0cJdyFHA.2064@TK2MSFTNGP09.phx.gbl...
> > XLBook.Worksheets("Sheet1").Select
> > XLBook.Worksheets("Sheet1").Range("A1").Copy
> > XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial 12
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> >
> > "D. Pirate Roberts" <dreadpirateroberts@bride.com> wrote in message
> > news:etaUBAdyFHA.1168@TK2MSFTNGP15.phx.gbl...
> >> I use the following code in vbs to copy and paste data from one Excel
> >> cell
> >> to another:
> >>
> >> XLBook.Worksheets("Sheet1").Select
> >> XLBook.Worksheets("Sheet1").Range("A1").Copy
> >> XLBook.Worksheets("Sheet1").Range("B1").PasteSpecial
> >>
> >> My code above does a regular paste, but I need to do a paste special
that
> >> only pastes values and number formats. I've created a macro in Excel
that
> >> does this and the vba code for it is:
> >>
> >> Range("A1").Select
> >>     Selection.Copy
> >>     Range("B1").Select
> >>     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
> >> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
> >>
> >> However, the vba code above does not work in vbs. It throws an Expceted
> >> Statement error at the first colon ( :Smilie:  in the PasteSpecial line. Does
> > anyone
> >> know how I can do this in vbs?
> >>
> >>
> >
> >
>
>

----------


## rochelles

I want to do the same thing, except, copy the last three characters in a cell (file extension, so it will always be three) and past it into another cell. Any ideas?

----------


## Lee Peedin

Tom,
It's a shame that VBScript doesn't have a built in method to retrieve
ActiveX/OLE constants.  I'm not posting this to try and start a flame
war on what scripting language is best, but the one I use (ooRexx) has
2 simple methods that make working with constants very easy to
incorporate into your script.

To retrieve a single constant:
myExcel = .OLEObject~new("Excel.Application")
xlCenter =  myExcel~GetConstant("xlCenter")
say 'xlCenter =' xlCenter

To retrieve all constants:
myExcel = .OLEObject~new("Excel.Application")
constants. = myExcel~GetConstant
do i over constants.
say i '=' constants.i
end

A good way to use the retrieval of all constants for a particuliar
ActiveX object is to run the above code once and save the results in a
text file (xlConstants.txt).  Then in any script that uses the ActiveX
object in question, all you have to do is load xlConstants.txt into
the .local enviroment.

Just posting this in the hopes that the "keepers" of VBScript might
learn something from the "grandfather" of scripting languages. :-)

Lee


On Wed, 5 Oct 2005 14:59:25 -0400, "Tom Ogilvy" <twogilvy@msn.com>
wrote:

>Excel VBA help on Pastespecial
>
>the values for the constants can be seen in the Object Browser in the VBE.

----------


## Tom Ogilvy

Actually, MS has provided a way to read typelibs.  Chip Pearson has sample
code on his site.  It certainly isn't as easy as your code, but once someone
has written the code, it isn't that bad I wouldn't think.

--
Regards,
Tom Ogilvy

"Lee Peedin" <lpeedinREMOVE@UPPERCASEnc.rr.com> wrote in message
news:7b2ak11qe8srp4dvkc0b66t58130gjedcm@4ax.com...
> Tom,
> It's a shame that VBScript doesn't have a built in method to retrieve
> ActiveX/OLE constants.  I'm not posting this to try and start a flame
> war on what scripting language is best, but the one I use (ooRexx) has
> 2 simple methods that make working with constants very easy to
> incorporate into your script.
>
> To retrieve a single constant:
> myExcel = .OLEObject~new("Excel.Application")
> xlCenter =  myExcel~GetConstant("xlCenter")
> say 'xlCenter =' xlCenter
>
> To retrieve all constants:
> myExcel = .OLEObject~new("Excel.Application")
> constants. = myExcel~GetConstant
> do i over constants.
>   say i '=' constants.i
> end
>
> A good way to use the retrieval of all constants for a particuliar
> ActiveX object is to run the above code once and save the results in a
> text file (xlConstants.txt).  Then in any script that uses the ActiveX
> object in question, all you have to do is load xlConstants.txt into
> the .local enviroment.
>
> Just posting this in the hopes that the "keepers" of VBScript might
> learn something from the "grandfather" of scripting languages. :-)
>
> Lee
>
>
> On Wed, 5 Oct 2005 14:59:25 -0400, "Tom Ogilvy" <twogilvy@msn.com>
> wrote:
>
> >Excel VBA help on Pastespecial
> >
> >the values for the constants can be seen in the Object Browser in the
VBE.
>

----------

