Excel Macro to not save to Excel when creating PDFs

I did some research on this, and I don't believe that it is possible to change the name of the excel file without saving it.  Have you checked to see if your PDF converter allows you to change the default savename option?  Maybe to the sheet instead of the filename?  If so, that's an easy change in the code without saving anything.  The only other option is to somehow reduce the size of your file so that your save takes place in a reasonable time.  Try removing all formating prior to doing the save, this might help reduce the time invovled.

HTH
Cal

Here's an easy way to remove formatting.

ActiveSheet.Cells.ClearFormats

                                              

1:

Problem is I have tons of vlookups and i need them to stay intect for each of the 151 loops.

What does ActiveSheet.Cells.ClearFormats actually do?

It just clears the formatting...colors....borders...fonts...bold..underline...etc....  These increase the size of the file, so if you eliminate them, the size decreases, which reduces the save time.

Since you have many vlookups, the recalc might be what is slowing your down as well. You might want to think about doing the following.

Copy pastespecial values.

Save as a temporary name that would fit your PDF naming needs.

HTH
Cal

activesheet.cells.clearformats
activesheet.cells.copy
activesheet.cells.pastespecial xlpastevalues
activeworkbook.saveas "Temp Name.xls"
                                              

1:
2:
3:
4:

Okay.... lets expland on this

How do I get the macro to:

a) change the tab name to be the name present in CELL G1
b) then the filename is equal to the tab name
c) then save as PDF...without saving as XLS

Just reading your message again, I would suggest you set this process up in it's own workbook.

Open workbook, remove formats, copypaste values, save under temp name. close workbook, iterate the loop, and so the same again.

Was this comment helpful? Yes No flickimp1717: Okay.... lets expland on this

How do I get the macro to:

a) change the tab name to be the name present in CELL G1
b) then the filename is equal to the tab name
c) then save as PDF...without saving as XLS

The only problem is that step b) requires a save of the file.  The ONLY way to update the filename is to save the file.  My original suggestion was to check and see if the PDF software has an option that allows you to use the tab name instead of the filename.

Ok...

At what point do I add :

Open workbook, remove formats, copypaste values, save under temp name. close workbook, iterate the loop, and so the same again.

to my original code?

Because...I dont want to lose the formuals in my master code...

By the way using DoPDFv.6 which is free so cant alter the file thing

OK,  Will need to understand a few more things in order to get down to coding this.

Looks like your code is cycling through the values in column E of the titles worksheet, and saving the sheets under this name, then converting to PDF via the printout function.

You array select a bunch of sheets, then you activate title, which would ungroup the sheets, then you copy them, but never paste them anywhere?  What exactly are you trying to accomplish with piece of code?

Sheets(Array("Title", "EM", "EL", "DC", "OP_1ST", "OP_FU", "FIRST_TO_FOLLOWUP_RATIO", "CASSIUS_AE", "UHL_AE")).Select
Sheets("TITLE").Activate
Sheets(Array("Title", "EM", "EL", "DC", "OP_1ST", "OP_FU", "FIRST_TO_FOLLOWUP_RATIO", "CASSIUS_AE", "UHL_AE")).Copy
 

                                              

1:
2:
3:
4:

Hi

As you have said, thats what I am trying to do:
Looks like your code is cycling through the values in column E of the titles worksheet, and saving the sheets under this name, then converting to PDF via the printout function.

The aim is:
Start ar first value in column E, to determine main lookup for all the sheets.
Vlookups kick in and update all the sheets
I then want that to be saved as a pdf file.
then next loop in the E column

Maybe there was no need for me to have the copy bit in my code?

Is there a simple coe that woulg copy and paste values on all the sheets without writign a code for each one?

Give these revisions a try.

HTH
Cal

Dim wb As Workbook, ws As Worksheet
Dim cell As Range
 
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Title")
ws.Cells.ClearFormats
 
For Each cell In ws.Range("E2", ws.Range("E65535").End(xlUp))
    ws.Range("G10").Value = cell
    wb.SaveAs Filename:=ws.Range("G1").Value
    wb.PrintOut copies:=1, ActivePrinter:="doPDF v6:", Collate:=True
Next cell
 
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 
MsgBox ("Job Done")
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

And now that I think about it a little more, removing your formats prior to printing to PDF will speed up the save, but will eliminate the formats that you need to produce the PDF, which makes no sense either.  You will need to remove the code that removes the formats.  Same problem occurs for my other suggestion, with copying and pasting values.  Since you cycle to the next value in E, you need the vlookups to refresh at that point.  I don't there is a whole lot we can do to speed this process up.  My suggested revisions will speed it up some, but it's still needs to recalculate all the formulas prior to the save, and you have to do the save to update the filename for the pdf conversion.

Cal

Dim wb As Workbook, ws As Worksheet
Dim cell As Range
 
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Title")
 
For Each cell In ws.Range("E2", ws.Range("E65535").End(xlUp))
    ws.Range("G10").Value = cell
    wb.SaveAs Filename:=ws.Range("G1").Value
    wb.PrintOut copies:=1, ActivePrinter:="doPDF v6:", Collate:=True
Next cell
 
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 
MsgBox ("Job Done")

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:

Hi

Thanks for that, but the formulas are not refrrshing as they should.
ie:
The first file should say C82xx1 in the file name but instead gives a blank
Its almost as if the code isnt recalculating?

Thanks

I changed the code to

For Each cell In ws.Range("E2", ws.Range("E152").End(xlUp))
    ws.Range("G10").Value = cell
    wb.SaveAs Filename:=ws.Range("G1").Value

but it stops after the first 2 files???? and doesnt do the whole 151?
why is that?