Export PDF, File Name Cell on Another Worksheet

Hi There,

I am working on a script to export two separate worksheets to separate PDF files in a specific folder, with the file name as a cell referecen on the active sheet for the first printout and the filename of the "next" worksheet as the same filename with _Lab added to the file name.

Part 1 is working perfectly:

Please Login or Register  to view this content.

Part 2 is working perfectly to add the _Lab component still on the active sheet:

Please Login or Register  to view this content.

When I try to have it move to the next worksheet, add the _Lab but still refer back to the previous worksheet for the file name beginning, i get errors:

Please Login or Register  to view this content.

What am I doing wrong?
Les

Both of them give me errors. Is it possible this portion is the issue?

Please Login or Register  to view this content.

Worksheets(ActiveSheet.Index + 1).Select

I found this piece online and haven't had it working yet. Is there a better way to move to the next sheet before the second PDF is created?

You never said what the error was but where is your

Please Login or Register  to view this content.

after you select the next indexed sheet?
BTW

Please Login or Register  to view this content.

will only error when the last Sheet is the active Sheet

The worksheets are back to back. The first one allows the user to paste in a record number and self populates. This is when I want the user to be able to hit a button which will export a pdf of the active sheet named the cellreference.pdf THEN export the next worksheet as a PDF called cellreference_Lab.pdf

Does that answer your question? I'm kind of a inexperienced and not quite sure how to determine where the error is. When I run the Macro it gets a Compile error: Invalid or unqualified reference and highlights

Please Login or Register  to view this content.

.ExportAsFixedFormat

Attach a sanitized copy of your file so we can have a check.

Here is a mock up.

Basically I want to be able to export the first print tab to a PDF in mypictures/current month. The filename should be the range of B3 contents.pdf.

The second export should take place to the same folder, to the same cell range name but have B3 contents_Lab.pdf

Does that help?

You can try the attached.
Note the info in the TextBox.

Good morning,

Thanks for all of your help. When I edit with my path, it seems to work but after the bar pops up saying its publishing, then it crashes with the following error. Your code, edited with my path is below. Did I miss something?

Please Login or Register  to view this content.

Attachment 618862

It works in the mockup perfectly but as soon as I add my path it crashes.

I'm sorry if I'm confusing everybody. My issue is when someone says add your path here, I'm novice enough that I "hope" I am copying and pasting in the exact correct portion of my path into the exact correct spot in the code.

Let me summarize the portion of the issue I am trying to do. I have code that works successfully to export a PDF file into the correct folder. It works perfectly but the file name is only _Lab with no record number. The code that is currently working is below. I can either keep the sheet active and refer to the other sheet for the filename only, or I need to rework the code to include the worksheet names as you mentioned above.


-sheet 1 is called "Template", and has a field where a record number is pasted in cell D11
-sheet 2 is called "Lab Template" updated automatically, is a locked sheet, it has the record number in cell E1 but this cell is a formula referring back to
sheet 1
-the script needs to export the sheet as a PDF and be named as the record number in D11 on the Template sheet e.g. record#_Lab
- the code Jolivanes is perfect except that I cant keep his workbook and so I need to add the path to save it which would be in My Pictures/Current Month for any user of the workbook regardless where they are on the network.


I hope that helped to clarify and not to confuse futher.

The pictures folder exists, as does the current month folder. If I F8 through the code line by line it crashed on the line:

Please Login or Register  to view this content.

here is the entire code I am running when that happens:

Please Login or Register  to view this content.

What if you unlock your "Lab Template" Sheet?

Oh just saw your question about the immediate window. When I question it I get this except "myprofile" is actually my name

?Environ("USERPROFILE") & "\Pictures\current month\"
C:\Users\myprofile\Pictures\current month\

I would say the path portion is working fine.

I have it unlocked currently. I thought that might be causing issues too.

OK great. I was just watching a video on this ha ha. Here is what I get in immediate when I run that through:

Please Login or Register  to view this content.

Not sure what that last line is....

I don't even know what that last line with the forward slash is referring to or where it comes from. It doesn't seem to have anything to do with the script.

OK I got it to work! Its now just exporting the 4th and 5th worksheet instead of the 3rd and 4th.

What do I edit to back that up by one?

Which Sheet is the Active Sheet (=visible on your desktop) when you run the code?
The code in the attachment I supplied should save the Active (=visible) Sheet and the next Sheet to the right as PDF.
If it now saves Sheets 4 and 5 you are running it when Sheet 4 is active I would think.

I'm so confused. Here is where I am at. I think part of the problem is the script crashes when the file already exists in that folder. I'm not sure what to do about that but for not I have been deleting the created files before trying to run it again. That moves the script along, it saves the first file and appears to WANT to name the second file correctly but it still crashes.

Here is the current code:

Please Login or Register  to view this content.

Here is the current immediate window:

Please Login or Register  to view this content.

The only place in the entire workbook the words "Quality Check" exist are on the template worksheet in cell P2. I can't see any reference to that cell in the Macro. I'm lost.

So thankful for your patience.