Pasting a row as values in VBA

[jshaw82]

Hi All,

As in the title I am trying to be able to paste a row using VBA. Normally this wouldn't pose me a problem but I have come across a slight problem I'm hoping someone can help me with.

User opens up a different spreadsheet. This can be from a file they have saved, or from an email attachment. They select a row that contains data thay want to paste into my spreadsheet. Run a code and it does it for them. This is where my problem lies:

If the user has opened the file they are copying the data from, from their desktop, the paste option is:

Please Login or Register  to view this content.

However, if the user has opened the file as an email attachment the above code does not work (as the file is in protected view), so the following code can be used:

Please Login or Register  to view this content.

The only code that works universally is :

Please Login or Register  to view this content.

but when I use that it pastes from the data as is with all linked references from the cells etc, which I don't want. An easy solution would be to tell them to make sure they open it as 'enable editing' but the people using it need this as simple as possible.

Any help greatly appreciated

John

[Norie]

John

How are you copying?

Also, where are you pasting to?

[jshaw82]

Either select row and CTrl+C or Right click row + Copy,

I'm pasting into a worksheet, here is the full code so far:

Please Login or Register  to view this content.

[Norie]

So the user is copying manually.

Have you considered having the user selecting the row to copy and then using code to do the rest?

For example this code puts the value from the row the user selected in an array.

Then it puts the values from that array in the destination row.

So it's basically doing a copy and paste special values.

Please Login or Register  to view this content.

[jshaw82]

I'm copying the data from one workbook (that is distributed by someone else) to mine. I have just tried with the code you have suggested but it is pasting the cell that is selected from my workbook.

[Norie]

How exactly did you try what I posted?

[jshaw82]

I opened the source workbook. Highlighted the row to copy. Went to the destination Workbook (my book) and pressed the button that I have associated with the code you have just suggested. I also ran it step by step from VBA editor.

[Norie]

You would need to run it from the destination workbook, which you could do via the Macros dialog on the developer tab.

Of course, that would only work if the source and destination workbook were opened in the same instance of Excel.

[jshaw82]

I think this is just going to make it harder for the user, thanks anyway

[jshaw82]

So I've come up with this, to try to overcome the issue:

Please Login or Register  to view this content.

However its coming up with a "Runtime error 424 object required" error on the bit i've underlined. When I go back the to book and select paste the right options are there for me to use and the data is still copied to the clip board. Where is it going wrong this time?

[Norie]

Have you considered having the user select the row to copy using Application.InputBox?

Please Login or Register  to view this content.

[jshaw82]

Hi Norie,

Thanks for your help.
I can see where you are coming from with your code, i need to make this as easy as possible. I am looking at another possible method that means the user has to manually copy and paste but I just know that they will do a standard paste which will paste linked values and completely mess the rest of the sheet up. I kinda got my other code to work but for some reason it looses the clipboard if it errors

Please Login or Register  to view this content.