Save worksheet when Set Print Area is being used

[ipbr21054]

Hi,

I use the code below & works fine.

Now i wish to use the same code on another worksheet BUT on this sheet i use the Set Print Area
The are thatonly needs to be saved is A1:K23
Rows after K23 have values in etc BUT i dont want them on the saved pdf sheet

Please advise how i can continue
Thanks

Private Sub CommandButton1_Click()
ThisWorkbook.Worksheets("PRINT LABELS").Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET
ThisWorkbook.Worksheets("PRINT LABELS").Range("A3") = Me.TextBox2.Text ' ENTERS PCB NUMBER TO WORKSHEET
Unload GenerateForm
  Dim sPath As String, strFileName As String
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & Range("B3").Value & " " & Range("A3").Value & ".pdf"
                                                                             
    With ActiveSheet
    If Range("AB1") = "" Then
    MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
    Exit Sub
    End If
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
  MsgBox "PDF HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
  
  End With
  
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\EBAY PDF\"
  strFileName = sPath & Range("AB1").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
 
End Sub

 

[HaHoBe]

Hi.

it works for me to add the range to printout for the sheet like

Private Sub CommandButton1_Click()
  Dim sPath As String
  Dim strFileName As String
  
  With ThisWorkbook.Worksheets("PRINT LABELS")
    .Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET
    .Range("A3") = Me.TextBox2.Text ' ENTERS PCB NUMBER TO WORKSHEET
  End With
  Unload GenerateForm
                                                                             
  With ActiveSheet
    If .Range("AB1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & .Range("A3").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "PDF HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
    
    sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\EBAY PDF\"
    strFileName = sPath & .Range("AB1").Value & ".pdf"
  End With
  
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
 
End Sub

And if the sheet to printout is PRINT LABELS you may use

Private Sub CommandButton1_Click()
  Dim sPath As String
  Dim strFileName As String
  
  With ThisWorkbook.Worksheets("PRINT LABELS")
    .Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET
    .Range("A3") = Me.TextBox2.Text ' ENTERS PCB NUMBER TO WORKSHEET
    Unload GenerateForm
                                                                             
    If .Range("AB1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & .Range("A3").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "PDF HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
    
    sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\EBAY PDF\"
    strFileName = sPath & .Range("AB1").Value & ".pdf"
  End With
  
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
 
End Sub

Ciao,
Holger

 

[ipbr21054]

Im confused as both the codes you advise are the same ?

 

[HaHoBe]

Hi,

not exactly: the first has 2 With clauses (one for PRINT LABELS, one for ActiveSheet), the second just uses one while specifying PRINT LABELS as the working sheet to receive data and being printed.

Holger

 

[ipbr21054]

Sorry my mistake

will look now thanks