Ask a new question

Excel doesn't paste when I run my VBA code a 2nd time

Hello,

With VBA, I work on a workbook that creates a new workbook in a new instance of Excel, copies the data from the original to the temporary workbook, do some update the data and copies back the transformed data from the temporary to the original.

This works fine, except when I run my code a second time. The second paste does not operate any more even if I try it manually with once cell. 
Even worse, if I save my original workbook after the 1st run, close it and restart the computer. I will always have the same problem.

How come?!?

PS: For info, I create a new instance of Excel because the update calls an Excel add-in (Bloomberg) that can't be run in an application that is already running code. It's a way of having two applications running in the same time.

Thank you.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Typically, it helps to post your code, but it sounds like your worksheet is being protected, with locked cells.

Bernie

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Bernie,

The code is too long to be copied here. So here is a link to a text file containing the code:

Basically what I do is copy data from an Access db (about 200x80 cells), copy it to the current Excel workbook. Transform it a bit, then copy it to a new workbook in a new Excel.

After a first successful run, it works fine, but at this point, just after having pasted the data in the new wkb, any paste from the new wkb to the original would fail.

Is it because the memory is not released, then it would work after a reboot, but it doesn't. o_0

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

So? Did the code help resolving this issue?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I could not open the link..

 

So what you are saying is that first time when you run the code, it creates a new instance of Excel, copies data from source to the new instance then it copies back from the new instance to the original instance...

but when you run the code a second time then it only copies to the new instance and does not copy back?

 

if you are familiar with VBA then remove all the extraneous code (in a copy of course) and just run the core code.

 

Does your code close the instance opened before you run it again? If you create an instance every time then second time around the code will not find the first instance and create a new one... is that the source of the problem. Also between instance cut and paste may not work as it works within the same instance.

If this response answers your question then please mark as Answer. It helps others who browse.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Sheeloo,

Thank you for giving me another perspective. It helps.

You understood well what I am doing. To answer your question, yes the code does close the new workbook as well as as the new instance of Excel right after copying back the data and I have the exact reference to this new instance.

I was thinking of saving the new workbook temporarily, close it with the new instance of Excel and then open it again in the first Excel. Therefore I will copy my data within the same Excel.

But before that, I will try removing part after part of my code and see which one is interfering.

Referring to your last phrase, how come the copy-past "may" not work as well as within the same instance? What's cool about coding is that you control what you do, so if you are right, I will never copy data between instances again. It works or not, nothing in between.

But what I find crazy is that I can't even copy the data manually, not even once cell, and this even after stopping the code. And if I save the file after the 2nd run and restart the computer, The code will never be able to copy the data as it did the 1st time. o_0

Thanks again.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Alright! Here are the results of my debugging:

I removed all my code and left only the part that creates the new instance and copies the data forth and back.

But even without code at all, the sheets doesn't accept any data from an external instance.

It's like the sheet has been protected after the 1st run. But its properties say that it's not protected. However the cells of this worksheet are shown as "Locked" in the Protection tab property. But any new workbook has this value by default and I always worked this way.
When I try to unlock the destination cells, it tells me : "Too many different cell formats".

my Solution:
Even if I didn't figure out the reason, the original workbook accepts data to be copied as a link. So I will copy it as a link. Copy again this copied data and paste in place as values. It's not clean, but at least faster than any other solution.

Thank you again for your help.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

my Solution that I suggested in my previous message that copies the values first as a link then as values is no reliable after all. Sometimes it works sometimes not with the same conditions. 
The past as link past a range that has {=Excel.SheetMacroEnambled.12|Book1!'!Sheet1!R1C1:R108C23'} as formula and that shows #N/A as a result.

The solutions that remains is to open the second workbook within the same instance.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hello,

 

Is it possible for you to share the workbook with the code? Do delete any personal data.

 

Also pl. see http://answers.microsoft.com/en-us/office/forum/office_2010-excel/problem-with-copy-paste-in-excel-2010/cca7eb06-4815-4d3b-a9c1-08f0ddecf0a2

 

We need to figure out what changes between first and second run...

 

Regards,

Sheeloo

If this response answers your question then please mark as Answer. It helps others who browse.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Sheeloo,

Here is a public link to the file: http://www.2shared.com/file/f74UHNn4/Stats1Debug.html 
(Use the blue squared download button)

To generate the error,
- Go to the sheet "ConsoP1" and then click on the button New located on this worksheet.

- The code will open a new instance of Excel, copy the cells from the worksheet "myData" to the new workbook, then simply copy it back to the original workbook in the "myData" worksheet and close the new Excel with new its workbook.

The first time you will see the data copied back into the sheet "myData" and the second time you will see myData empty as a result of nonfunctional paste action.

You can also try, after the second run, to open manually another Excell and write something and copy this cell to the the file Stats1Debug.xlsm. this will not work.

If you save the file, the copy paste action will never work again even on a 1st run.

I made as simple as I could

Thank you again for your help.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hello,

 

I tested your code... had to do some tweaking in object declaration to make it work...

 

Essentially I changed the following in gatherData routine and it works every time. In my view the basic issue is making sure that what you copy remains available at the time of pasting. I have made the bare minimum changes to make it work. You will have to rewrite it to make sure that you have got the write code;

(commented out a few lines and added a few)

 

    With wkbTmp.Worksheets(1)
        ThisWorkbook.Worksheets("myData").Range(ThisWorkbook.Worksheets("myData").Cells(1, 1), ThisWorkbook.Worksheets("myData").Cells(10, 10)).Copy
        .Paste
        '...

'Added
        .Copy
ThisWorkbook.Worksheets("myData").Activate

' Commented out
'        .Range(.Cells(1, 1), .Cells(10, 10)).Copy
'        ThisWorkbook.Worksheets("myData").Select
'        ThisWorkbook.Worksheets("myData").Cells(1, 1).Select
'        ThisWorkbook.Worksheets("myData").Paste
'Added
ActiveSheet.Paste
    End With

If this response answers your question then please mark as Answer. It helps others who browse.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info

Last updated October 15, 2022 Views 3,148 Applies to: