VBA to convert pdf

[Abdulkhadar]

Hi Excel Experts.
I want to save excel sheet as pdf, file name is as per cell C15, location -desktop.
Thank you

 

[jolivanes]

Sub Maybe_So()
Dim myDir As String, i As Long
myDir = "C:\Abduls PDF Files\"    '<---- Change as required
    With ThisWorkbook
        For i = 1 To .Worksheets.Count
            .Worksheets(i).ExportAsFixedFormat 0, myDir & .Worksheets(i).Cells(15, 3).Value & ".pdf"
        Next i
    End With
End Sub

It assumes that all print ranges have been set as you don't mention it.

 

[jolivanes]

Thanks for the update and good luck

 

[Micron]

Why not just do File > Print and choose Microsoft Print to PDF, enter the file name and choose the location?

 

[Abdulkhadar]

Why not just do File > Print and choose Microsoft Print to PDF, enter the file name and choose the location?

Thanks for quick reply. there are so many sheets, so I want to save the time and create a button to save as pdf with C15 cell name.

 

[Micron]

There are several suggested threads at the bottom of this one. Did you review any of them, or do your own search for using a command button to print sheet as pdf? This topic has been answered a gazillion times on the internet so there's really no need to write a new sub from scratch.

 

[jolivanes]

As Micron suggested, there are lots of threads requesting help for the same, as it sounds anyway, problem.
In you first Post, you say that you want to "save excel sheet as pdf" which indicates a single sheet.
In your next Post you say "There are many sheets".
So what would be your next question?

 

[Abdulkhadar]

As Micron suggested, there are lots of threads requesting help for the same, as it sounds anyway, problem.
In you first Post, you say that you want to "save excel sheet as pdf" which indicates a single sheet.
In your next Post you say "There are many sheets".
So what would be your next question?

Thanks for reply, There are many sheets in a workbook, Every sheet has a File name at C15 cell. so, I want to save as pdf every sheet as per Name as cell C15. at a time all sheets or one by one sheet.

 

[jolivanes]

Does the C15 Cell also have the path or just the filename, with or without extension as the extension is known as pdf?
Re "all sheets or one by one"
How will this be determined if it means that you might want to save a single sheet or all sheets or a certain amount of sheets?

BTW, you don't need to quote for me. I find it just extra clutter not needed at all.

 

[Abdulkhadar]

It assumes that all print ranges have been set as you don't mention it.

'Sub CopyData()
  Dim ws As Worksheet
    For Each ws In Worksheets
    If ws.Name <> "Data" And ws.Name <> "Centre" Then ws.UsedRange.Offset(18).ClearContents
  Next ws
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("F" & Rows.Count).End(xlUp).Row
    Dim AccNum As Range
    For Each AccNum In Range("F2:F" & bottomA)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = AccNum.Offset(0, -4)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = AccNum.Offset(0, -3)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = AccNum.Offset(0, -2)
        Sheets(CStr(AccNum)).Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = AccNum.Offset(0, -1)
    Next AccNum
    Application.ScreenUpdating = True
ActiveWorkbook.Save

ActiveSheet.PageSetup.PrintArea = "$A$1:$E$" & Cells(Rows.Count, "B").End(xlUp).Row

Sorry for incomplete information. C15 cell is just file name. I use above code. This code copy data from "Data" sheet and other sheets. print area set from A to E column up to last used B column cell except "Data" and "Centre" sheets. I want to save all sheets as pdf except sheets namely "Data" and "Centre" sheets. I think you are understand what is my goal. Thanks once again.