PasteSpecial vba problem

[Damo71]

Hi

I'm having trouble using PasteSpecial in a macro:

Sheets("Sorter").Range("ImportStart").PasteSpecial Format:="Text"

I get an "Application-defined or Object-defined error" message.

InportStart is a cell I've named on the sheet "Sorter". I copy a table from an external database and then run a macro to paste it into the correct place in my workbook.

When I've recorded the macro it works fine because it Selects the various cells I do and does every little action (the macro records me using "Application.Goto Reference:="ImportStart"" and it does the paste with no problem) - but I don't really want the macro to "Select" the cell because I don't want the user to lose his place when he runs the macro.

Any ideas why i can't pastespecial using the above code?

(I'm using Excel 2003)

Thanks

 

[Norie]

There isn't a 'Format' argument for paste special, the first argument is called Paste and the values for it are things like xlPasteAll, xlPasteValues, xlPasteFormats...

You probably want xlPasteValues.

 

[Damo71]

There isn't a 'Format' argument for paste special, the first argument is called Paste and the values for it are things like xlPasteAll, xlPasteValues, xlPasteFormats...

You probably want xlPasteValues.

Thanks Norie

Normally that is true, But when the data on the clipboard isn't excel data PasteSpecial gives you different options...
Furthermore, in my macro this actually works:

Application.Goto Reference:="ImportStart"
ActiveSheet.PasteSpecial Format:="Text"

But, as i said, I don't actually want Excel to goto the cell if i can help it.

Any suggestions?

 

[Norie]

It might work but I wouldn't keep it if I was you.

It's probably just some sort of strange weirdness.

By the way you shouldn't need to use Goto or ActiveSheet.

Try using the range name on it's own.

Range("ImportStart").PasteSpecial Paste:=xlPasteValues

You might want to check the previous code as well, in particular the copy part.

Sometimes, for whatever reason, something in the code clears the clipboard so there's nothing to paste.

When I think about it, GoTo might actually be doing that.

 

[Damo71]

It might work but I wouldn't keep it if I was you.

It's probably just some sort of strange weirdness.

By the way you shouldn't need to use Goto or ActiveSheet.

Try using the range name on it's own.

Range("ImportStart").PasteSpecial Paste:=xlPasteValues

You might want to check the previous code as well, in particular the copy part.

Sometimes, for whatever reason, something in the code clears the clipboard so there's nothing to paste.

When I think about it, GoTo might actually be doing that.

Thanks Norie

Values doesn't work I'm afraid. And I don't do the copying from Excel. I manually copy info on a database, return to excel and run the macro. Also, as I mentioned the code does work (it doesn't clear the clipboard) when Goto is used.

 

[Jonmo1]

Unfortunately, when dealing with outside apps, sometimes you don't have a choice.

The properties available (when clipboard contains non excel data) are only available to the Worksheet Object, Not the Range object.

So ActiveSheet.PasteSpecial Format:="Text" will work, but
Range(...).PasteSpecial Format:="Text" Will not.


Perhaps if you set the format of the destination cell to text FIRST, then paste...

Range("ImportStart").NumberFormat = "Text"
Range("ImportStart").PasteSpecial

 

[Norie]

Oops, my bad.

Didn't see the bit about the external database.

Is there no way you can get the data without copying it from the database?

How are you doing that anyway? Manually?