Create and Name PDF|VBA & Macros|Excel Forum|My Online Training Hub

Hi Bob,

Without seeing all your code I can't be sure, and I can't run it to test it and see exactly what error messages you are getting, always best to include the workbook if you can to help us 🙂 ,  but 

1) Does finalcol have a value?

2) Does Cells(3, finalcol + 20) contain anything?

Try debugging your VBA with Debug.print

Before creating the PDFFile string try this

Debug.Print Payperdate

Debug.Print finalcol

Regards

Phil

No worries Bob.

I don't find anything logically wrong with your code, it doesn't generate an error on me.  However, the value for Payperdate is empty on every sheet.  You said that you get this value from asking the user for input which is fine, I guess I'm just missing that?.  The code still works even if Payperdate is empty.  You just get PDF file names like Mileages_.pdf

But do you need to store this on every sheet?  Why not just store it on just one sheet (called Data) and access it that way e.g.

Sheets("Data").Range("A1").Value

Which brings up the point that the macro uses the data on the ActiveSheet.  Anywhere you see code like this

ActiveSheet.Range("H6").Value

it's accessing the data on the currently active sheet.  Which isn't necessarily the one you want to be getting data from.  When you run your macro, do you have the correct sheet active?

So, I haven't encountered an error.  Maybe you should go through my post on debugging, and enter some breakpoints then step through the code to examine variables and what is happening.

Also, it took me a minute to figure out you had assigned CTRL+C to one of your macros.  It's good practice not to re-assign predefined shortcuts for your macros.

Cheers

Phil

Hi Bob,

As we'll be looping through every worksheet, but only saving some as PDF's, we need to have a way to flag that a sheet is\isn't for exporting as PDF.

I didn't want to hard code sheet names into the VBA as potentially this would need to be changed every time we added, removed or renamed a sheet, 

I thought about having a value on each sheet that the macro could read e.g. A1 has the value "NoExport" (or whatever), but this would be too messy.  The macro would need to read the same cell on every sheet and we can't guarantee that the same cell won't have some other data in it.

What I have done is to change the name of any sheet you don't want to export, so that it starts with an underscore.  This also gives you a quick visual indication of what sheets will be exported, you don't have to go through each sheet checking a value in a cell.

Wrap the main part of our code in this loop

   For Each ws In ThisWorkbook.Worksheets

      If Left(ws.Name, 1) <> "_" Then

         ...  our code ...

      End If

   Next ws

In the attached workbook I've changed a couple of sheet names for testing purposes, you'll need to rename the correct sheets yourself.

In the workbook CurrentMonth was getting a value from H6 on every sheet, but it was empty.  So I've changed it to pick up the value of A1 on the Data sheet.  You can change this to whatever you wish.

Cheers

Phil

no worries 🙂