Intermittent Error 1004 on Selection.PasteSpecial

I found code from Microsoft to Export Data to PDF or XPS Using the Excel.ExportAsF​ixedFormat Method and was able to make it work with a macro I created but it only works every other time I use it. Each time it doesn't work I get a VB Run-time error '1004': PasteSpecial method of range class failed. I hit the debug button and find Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Transpose:=False highlighted. If I reset the project it runs fine. I think it has to do with sheet protection but I haven't been successful finding anything online to fix it. My code is below.

Please Login or Register  to view this content.

I have a second item that i would like to accomplish. Is there a way to have the file name of the out put for fileName = "C:\Users\Tony\Documents\Pool League\Export.pdf" set the file name based on a cell value on the MatchReport sheet?

Thanks for you help.

Hi Norie, thank you for replying. I tried your code modifications and it returns Run-time error '-2147018887 (80071779)': Document not saved.

Debug shows the following highlighted:

Please Login or Register  to view this content.

Tony

I didn't change that part of the code.

Are you sure the code is picking up a valid filename?

I just used this code as an example, I've no idea which cell on which worksheet the filename is in.

You'll need to change the sheet and range to meet your own needs.

Please Login or Register  to view this content.

Yes, I changed that to:

Please Login or Register  to view this content.

Hi Norie. I should back up a bit. When I first tried your code I received a Run-time error 1004': Method 'Goto' of object '_Application' failed. Debug showed Application.Goto Sheets("MatchReport").Range("A1") highlighted. Resetting after I removed this line and that was when I received the result in post #3.

However I posted too soon. I ony received that error once. The first time I reset the project to try again I received a Run-time error '1004': PasteSpecial method of range class failed and debug shows .Range("B1:P30").PasteSpecial Paste:=xlPasteValues highlighted. If I reset the project again and run the macro it appears to run without error but creates a blank pdf file named appropriately. Upon running 3rd time I get, without reset because there was no error, it goes back to the PasteSpecial 1004 error for .Range("B1:P30").PasteSpecial Paste:=xlPasteValues highlighted again.

Sorry, this is kind of confusing. Intermittent still, I guess you could say.

This is the code currently:

Please Login or Register  to view this content.

Hi Norie, I don't know how the range was removed when I put it into the post, it doesn't appear to be in the workbook. Sorry for that. I created an example that I have attached but I made sure that your original code is in there so you can see the results.

Thanks Norie, I appreciate your help very much.

Hi Norie,

After reading your last post I changed the path to just create the file in the root of the C drive. I got the same result as before. I have since repaired my office install in case there was something wrong. I tried the file again just now.

On the first attempt, the report is created in the C drive and with the correct name.
I received Run-time error '1004': Method of object '_Application' failed.
Debug shows

Please Login or Register  to view this content.

highlighted in the code.

I reset the project and run the macro again.

The report is not created.
I receive Run-time error '1004': PasteSpecial method of Range class failed.
Debug shows

Please Login or Register  to view this content.

highlighted in the code

Each time I reset it switches between the above errors.

I don't understand...

Hi Norie,

As a test, I separated the PDF printing code from the report creation code into three macros; your code for report creation, mine for report creation and the original code for PDF creation.

Using your code as follows:

Please Login or Register  to view this content.

I get Run-time error '1004': Method of object '_Application' failed and debug points to:

Please Login or Register  to view this content.

Using my original code for report creation as follows:

Please Login or Register  to view this content.

Works without errors every time.

The original code for exporting the MatchReport sheet to a PDF file is as follows:

Please Login or Register  to view this content.

Works without errors every time.

Maybe I just need to call the Print_PDF macro within the Create_MatchReport after the report is created correctly. The problem has now changed so I'll look around for the answer and post a new thread if I can't find what I'm looking for otherwise.

Thank you Norie for all of your help and patience. You showed me how to set the name of the output PDF file and I am grateful. I don't know why your code would work for you and not me. Maybe you have a different version of Excel or different OS? I have Excel 2010 32-bit and Win 7 Pro 64-bit, both fully up to date and running on a HP EliteBook 8560W, with 8GB of RAM. It's a good system and I don't have any problems with it otherwise.

For now I'll work with the two macros separated and look for a way to combine them.

I found the additional command I needed to call the Print_PDF macro. It now works exactly as I wanted it to. Code below:

Please Login or Register  to view this content.

Hmmm... Weird. I wonder why you weren't getting it before? Did you take a look at separating your code from the pdf code?

The outcome of my post is still what I wanted it to be no matter how I got there, so thanks for working through it with me. It's good to get exposure to other ways of writing code.

Tony

Glad to be of some help.

It definitely is most weird that it's working one minute and then throwing errors, inconsistent errors at that.

Anyway, if it's working that's the importatnt thing.

PS I did have one idea - the length of the filename? Perhaps there's some limit?

PPS I actuall found one cause of the document not saved error, already having the pdf open. That's why I was getting all the errors i mentioned in the last post.

I had the problem of the pdf file being already open too. It's caught me a few times. Now I'm wondering about additional code to catch that and have some dialogue pop up asking to rename the file or at least telling you to close the one that's open and run the report again... Back to the bone pile.

Good call, and I had though about that too but I need to print a hard copy of the report and then email it out so I do want it to open. After thinking about it, I think I need to make saving the file more versatile by providing the ability to select a cell value on the reports worksheet and then reference it in the macro somehow. This is such a continual work in progress.

I found the code to reference a folder location and place it as a value into a cell. Thanks to your help Norie, I modified the path of where to save the PDF file to reflect a cell. Thanks again.

For any others who find it useful, here is the code for a macro to browse to a folder path and save the path into a cell to be referenced as part of a save path in another macro.

Please Login or Register  to view this content.

Please Login or Register  to view this content.