VBA to create new worksheet based on cell info and also save as a PDF file.

GedSalter

New Member
Joined
Apr 24, 2019
Messages
25
Hi all<

I have a vba which creates the new worksheet no problem. But I want to add to the VBA by having it also save as a pdf file in the following location:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Ged\Disability\2019-2020\invoices.

I also want the PDF file to be named the same as the worksheet that was created.

This is what I have for that:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Add_Sheet()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim ws As Worksheet
Dim wh As Worksheet
Set ws = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
Set wh = Worksheets(Sheets.Count)
If ws.Range("e9").Value <> "" Then
wh.Name = ws.Range("E9").Value
End If
wh.Activate
Range("A1").Select[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] End Sub


This works great but when I try an add the save as PDF it wont do that side of it. Hope someone can help. I have never tried to have a VBA do two things before.

regards

Ged
[/FONT]
<strike>
</strike>
[/FONT]
 

joshman108

Active Member
Joined
Jul 6, 2016
Messages
310
Try this?

Code:
Sub Add_Sheet()Dim ws As Worksheet
Dim wh As Worksheet
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler






Set ws = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
Set wh = Worksheets(Sheets.Count)
If ws.Range("e9").Value <> "" Then
wh.Name = ws.Range("E9").Value
End If
wh.Activate
Range("A1").Select






strPathFile = "C:\Ged\Disability\2019-2020\invoices\" & wh.Name


' user can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")


'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If


exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub
I assume the location is in your C drive, but you can change it on this line if needed

Code:
strPathFile = "C:\Ged\Disability\2019-2020\invoices\" & wh.Name
 

GedSalter

New Member
Joined
Apr 24, 2019
Messages
25
Hi Josh.

I have been trying this. It creates a PDF but in "documents" folder only. I cant open the PDF because it says it doesnt exist. The file contains 0bytes
 

Forum statistics

Threads
1,078,466
Messages
5,340,484
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top