Issue with exporting to PDF

[ardykav]

I have had some code which has always worked fine. However now it keeps erroring for some reason, I have highlighted where the script is erroring. I am simply trying to export the sheet to a pdf.

The value in S2 was previously this
="AdminList "&TEXT(Controls!$B$2,"MMMM DD YYYY ")&TEXT(List!$M2,"h.mmAM/PM")

But i thought it was an error with that so I just changed it to text so it read "Admin List 1", this worked the first time that I ran it but after that it errored out again. I also have an admin list 2,3 and 4 and another bit of code that merges them and all have worked fine up until this point.
Admin List 1

I am guessing the error is with the file name but any help would be appreciated. I would prefer to have the formula as the file name but am flexible with what works

Sub admissionsource1()

Dim FolderPath As String

       
    Sheets("Adm SStats 1").Select
    [B]ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("S2").Value, _
        openafterpublish:=False, ignoreprintareas:=False[/B]
    Sheets("Adm SStats 1").Select
    Range("A1").Select
    

End Sub

 

[DanteAmor]

Change / by - in AM-PM

The value in S2 was previously this
="AdminList "&TEXT(Controls!$B$2,"MMMM DD YYYY ")&TEXT(List!$M2,"h.mmAM-PM")

 

[ardykav]

Thanks But that then makes it looks like this

AdminList September 06 2019 14.30A1-P1

 

[DanteAmor]

I don't really have problems with this

="AdminList "&TEXT(Controls!$B$2,"MMMM DD YYYY ")&TEXT(List!$M2,"h.mmAM/PM")

Try this

="AdminList "&TEXT(Controls!$B$2,"MMMM DD YYYY ")&TEXT(List!$M2,"h.mm")

 

[ardykav]

That will work but with the way its getting sent out it actually needs to have the AM PM after it and not be in 24 hour format.

You are right the first formula has always worked just lately it hasnt when I added multiple tabs

 

[ardykav]

It doesnt look like its to do with the date as its now erroring somewhere that hasn't got a date reference and also working where it does have it.

There are 12 tabs to be pdfd.

8 are identical apart from the time they show in cell S2. Is it possible that this duplication is causing the error?

Similarly the other 4 are identical apart from a date value in S1

Sorry I also left out they are being saved in Arrays in some parts. Would that be the problem?


Here is the code for the other part that puts the 4 tabs together

Sub PDFSourcestas()

Dim FolderPath As String
Set sh1 = Worksheets("List")
Set target = sh1.Range("M8")
If target.Value = "1" Then
 Sheets(Array("Admission Source Stats 1")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("S1").Value, _
        openafterpublish:=False, ignoreprintareas:=False
End If
If target.Value = "2" Then
    Sheets(Array("Admission Source Stats 1", "Admission Source Stats 2")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("S1").Value, _
        openafterpublish:=False, ignoreprintareas:=False
End If
If target.Value = "3" Then
    Sheets(Array("Admission Source Stats 1", "Admission Source Stats 2", "Admission Source Stats 3")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("S1").Value, _
        openafterpublish:=False, ignoreprintareas:=False
End If
If target.Value = "4" Then
    Sheets(Array("Admission Source Stats 1", "Admission Source Stats 2", "Admission Source Stats 3", "Admission Source Stats 4")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("S1").Value, _
        openafterpublish:=False, ignoreprintareas:=False
End If
End Sub

I don't really have problems with this

="AdminList "&TEXT(Controls!$B$2,"MMMM DD YYYY ")&TEXT(List!$M2,"h.mmAM/PM")

Try this

="AdminList "&TEXT(Controls!$B$2,"MMMM DD YYYY ")&TEXT(List!$M2,"h.mm")

 

[DanteAmor]

Maybe you need the reference to the sheet.

  Set sh1 = Worksheets("List")
  Set target = sh1.Range("M8")
  If target.Value = "1" Then
   Sheets(Array("Admission Source Stats 1")).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("Admission Source Stats 1").Range("S1").Value, _
          openafterpublish:=False, ignoreprintareas:=False
  End If
  If target.Value = "2" Then
      Sheets(Array("Admission Source Stats 1", "Admission Source Stats 2")).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("Admission Source Stats 1").Range("S1").Value, _
          openafterpublish:=False, ignoreprintareas:=False
  End If
  If target.Value = "3" Then
      Sheets(Array("Admission Source Stats 1", "Admission Source Stats 2", "Admission Source Stats 3")).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("Admission Source Stats 1").Range("S1").Value, _
          openafterpublish:=False, ignoreprintareas:=False
  End If
  If target.Value = "4" Then
      Sheets(Array("Admission Source Stats 1", "Admission Source Stats 2", "Admission Source Stats 3", "Admission Source Stats 4")).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("Admission Source Stats 1").Range("S1").Value, _
          openafterpublish:=False, ignoreprintareas:=False
  End If

 

[ardykav]

Bit of a weird one but it errors out and then when i go to debug and press play again it works and this applies to all parts of the code, is there a way to play again when it errors?

Maybe you need the reference to the sheet.

  Set sh1 = Worksheets("List")
  Set target = sh1.Range("M8")
  If target.Value = "1" Then
   Sheets(Array("Admission Source Stats 1")).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("Admission Source Stats 1").Range("S1").Value, _
          openafterpublish:=False, ignoreprintareas:=False
  End If
  If target.Value = "2" Then
      Sheets(Array("Admission Source Stats 1", "Admission Source Stats 2")).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("Admission Source Stats 1").Range("S1").Value, _
          openafterpublish:=False, ignoreprintareas:=False
  End If
  If target.Value = "3" Then
      Sheets(Array("Admission Source Stats 1", "Admission Source Stats 2", "Admission Source Stats 3")).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("Admission Source Stats 1").Range("S1").Value, _
          openafterpublish:=False, ignoreprintareas:=False
  End If
  If target.Value = "4" Then
      Sheets(Array("Admission Source Stats 1", "Admission Source Stats 2", "Admission Source Stats 3", "Admission Source Stats 4")).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheets("Admission Source Stats 1").Range("S1").Value, _
          openafterpublish:=False, ignoreprintareas:=False
  End If

 

[DanteAmor]

I can't find what the problem may be. In my tests the error does not appear.
You can try changing the name of the file for any text, which does not include the date and time, only one text.

 

[ardykav]

Yes I did that too, not to worry, thanks for all of your help